Excel 2002 - If Statement

[suechamps]

I'm working with 2 spreadsheets with 3 columns: Bank #(Col A), Branch #(Col B) and Branch Name(Col C). I need to identify those branches where the bank # matches, if so, does the branch # match, if so, return the branch name. I have tried the following:

=IF('[north listing 072606.xls]branchlisting.rpt'!$A$2:$A$700=$A5,IF('[north listing 072606.xls]branchlisting.rpt'!$B$2:$B$700=$B5,VLOOKUP(A:A,'[north listing 072606.xls]branchlisting.rpt'!$A:$C,3,FALSE),"NO BRANCH# MATCH"),"NO BANK# MATCH")

The problem that I'm having is that matches that I know exist are not returning correctly in the formula. I think the problem may be that the bank# is not unique. For example, in one spreadsheet are 4 instances of a bank# of 1 while in the other spreadsheet there are only 3 instances.
Is it possible to do what I'm trying to do??

Hope this makes sense.

Thanks in advance for any help,
Sue

 

[AnotherHiggins]

Let me make sure I understand what you want....

Sheet1 has the something like the following
[tt]Bank# Branch# Branch Name
1 1 a
1 2 b
2 1 c
3 1 d[/tt]

and Sheet2 has the following
[tt]Bank# Branch#
1 1
2 1
3 1[/tt]

and you want to pull the Branch Name from Sheet1 to Sheet2, right?

If I have that right, then you can use this:
[tab][COLOR=blue white]=sumproduct((Sheet1!A1:A100=A2)*(Sheet1!B1:B100=B1)*(Sheet1!C1:C100))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[suechamps]

John,

Thanks for the reply, but that did not work for me. Hopefully, here's a better explanation of what I'm trying to accomplish:

SHEET 1
Bank # Branch # Branch Name
1 2010 Portsmouth
1 3010 Topsfield
1 9010 Congress St
1 5010 Main St
2 2020 Fayetteville

SHEET 2
Bank # Branch # Branch Name
1 2010 Portsmouth
1 3010 Topsfield
1 9010 Portland
2 2020 Fayetteville

What I want to do is:
IF Sheet1 Bank# = Sheet2 Bank# THEN IF Sheet1 Branch# = Sheet2 Branch# THEN Sheet2 Branch Name

Hopefully, this makes it a bit clearer.

Again, thanks in advance for any help
Sue

 

[firefytr]

Can you explain..

For example, in one spreadsheet are 4 instances of a bank# of 1 while in the other spreadsheet there are only 3 instances.

.. and ..

..return the branch name..

Where do you want to return the branch name? In a compact list? Just identify the rows? This list you want to return is where the information is the same for all records across both sheets?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[AnotherHiggins]

I see. SumProduct doesn't like returning text. Just rename all your branches with numbers, it will work fine!

Just kidding.

Insert a helper column into Sheet1, column C. Use the formula =A2&B2 to concatenate the two fields being used in your lookup. Your sheet will look like this:
[tt]
Bank # Branch # BankBranch Branch Name
1 2010 12010 Portsmouth
1 3010 13010 Topsfield
1 9010 19010 Congress St
1 5010 15010 Main St
2 2020 22020 Fayetteville[/tt]

Now on sheet2, use this VLookup:
[tab][COLOR=blue white]=VLOOKUP(A2&B2,Sheet1!C,2,0)[/color]

You can hide Sheet1 Column C if you wish.

--
PS - Just an FYI for future reference: to make your columns line up when posting on Tek-Tips, you can use surround the table with [tt][/tt] tags.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.