Returning Value Based on Two Different Ranges
 

Hello,
I have an issue that my INDEX MATCH function can't seem to work out.

Column A (A1:A100) is a list of Names. The names can repeat.

Column B (B1:B100) is a list of Locations. The names can repeat.

Column C (C1:C100) is a list of Phone Numbers.


I am trying to create a formula that will return the result in column C (Phone number) based on the values in columns A and B

e.g. If I search for the name "John" and the Location "New York", and it finds both the name John in A50 AND New York in B50, it will return the phone number in C50

Hopefully this makes sense.
I can't get INDEX MATCH to work so I think this is above my skillset. Any feedback helps. Thank you.

Returning Value Based on Two Different Ranges
 

Hi,

Am Mon, 16 Mar 2015 10:54:51 -0700 (PDT) schrieb cardan:

Column A (A1:A100) is a list of Names. The names can repeat.

Column B (B1:B100) is a list of Locations. The names can repeat.

Column C (C1:C100) is a list of Phone Numbers.

I am trying to create a formula that will return the result in column C (Phone number) based on the values in columns A and B

e.g. If I search for the name "John" and the Location "New York", and it finds both the name John in A50 AND New York in B50, it will return the phone number in C50

try:
=INDEX(C:C,MATCH("John"&"New York",A1:A100&B1:B100,0))
and insert the array formula with CTRL+Shift+Enter


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Returning Value Based on Two Different Ranges
 

That worked!
1. I didn't know you could join ranges like that and,
2. I totally spaced on the array function.

Thank You Claus!

Returning Value Based on Two Different Ranges
 

Hi,

Am Mon, 16 Mar 2015 11:13:26 -0700 (PDT) schrieb cardan:

1. I didn't know you could join ranges like that and,

if you have numbers in the columns you must add another sign, because
31&5 is the same string as 3&15.
So you have to change the formula for example to:
=INDEX(C:C,MATCH(31&"*"&5,A1:A100&"*"&B1:B100,0))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Returning Value Based on Two Different Ranges
 

Very good to know. Thanks again.