Multiple Criteria Lookup Question

[Guest]

How do I do a table lookup with more than 1 criteria?

On Sheet1, I have a range containing Last Names, First Names, and Phone
Numbers. For example,

1 Last_Name First_Name Phone_Number
2 Smith Bob 555-1231
3 Jones Tom 555-6789
4 Smith Sue 555-2345
5 Anderson Tom 555-3456
etc...

This table is not sorted. There are may be multiple people with the same
last name (e.g. many Smiths) and multiple people with the same first name
(e.g. many Toms), but the combination of last name and first name is unique
(e.g. can't have two Tom Smiths).

What formula can I use to return the phone number when a last name and first
name are entered. For example, on Sheet2 I have

A B C
1 Last_Name First_Name Phone_Number
2 Lucas Jan ?

What formula can I enter in cell C2 to return the phone number from the
previous table? Note: The Last_Name, First_Name combination entered here are
guaranteed to be in the prior table.

I know I can add a helper column to the first table where I concatenate last
name and first name together to form a single unique string, but I really
don't want to use a helper column if I can avoid it.

 

[RagDyer]

Try this *array* formula in C2:

=INDEX(Sheet1!$C$2:$C$10,MATCH(1,(Sheet1!$A$2:$A$10=A2)*(Sheet1!$B$2:$B$10=B
2),0))

And drag down to copy.

Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


How do I do a table lookup with more than 1 criteria?

On Sheet1, I have a range containing Last Names, First Names, and Phone
Numbers. For example,

1 Last_Name First_Name Phone_Number
2 Smith Bob 555-1231
3 Jones Tom 555-6789
4 Smith Sue 555-2345
5 Anderson Tom 555-3456
etc...

This table is not sorted. There are may be multiple people with the same
last name (e.g. many Smiths) and multiple people with the same first name
(e.g. many Toms), but the combination of last name and first name is unique
(e.g. can't have two Tom Smiths).

What formula can I use to return the phone number when a last name and first
name are entered. For example, on Sheet2 I have

A B C
1 Last_Name First_Name Phone_Number
2 Lucas Jan ?

What formula can I enter in cell C2 to return the phone number from the
previous table? Note: The Last_Name, First_Name combination entered here
are
guaranteed to be in the prior table.

I know I can add a helper column to the first table where I concatenate last
name and first name together to form a single unique string, but I really
don't want to use a helper column if I can avoid it.

 

[Guest]

Thanks, That works. I've used array functions quite a bit before, so I think
I even understand why it works.

 

[RagDyer]

Appreciate the feed-back.
--


Regards,

RD