What's wrong with this Match formula

  • Thread starter Thread starter John
  • Start date Start date
J

John

I wish to lookup a phone number and return the name associated with this
number. Thus I have 2 range names (1) PhoneNumbers, (2) PhoneNames. The
number I wish to return the name for is in B5 but it returns a #N/A value
and not sure why. See formula I am using below

Note both range names are the same length. There are some leading Zero's
with the Phone Numbers

=IF(B5=0,"",INDEX(PhoneNumbers,MATCH(B5,PhoneNames,0)))


Thanks
 
It seems like you're doing it the wrong way around!
Try:
=IF(B5=0,"",INDEX(PhoneNames,MATCH(B5,PhoneNumbers,0)))

HTH
 
Try something like:
=IF(B5="","",INDEX(PhoneNames,MATCH(TEXT(B5,"0000000"),PhoneNumbers,0)))
assuming the PhoneNumbers are 7 digits (padded with leading zeros)
and the input is just numbers (wo leading zeros)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IF function says too many nested 1
Index Match on Columns and Rows 0
With F1 value, look at A, if match or partial match return B 5
Index Match Problem 3
Lookup returning wrong results 0
match formula 2
INDEX MATCH #N/A Error 3
Checking if data exists another table and, if yes, extracting a value 0

Back
Top