What's wrong with this Match formula

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
 
A

Ardus Petus

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

HTH
 
M

Max

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)
 
Top