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
--
AP


"John" a écrit dans le message de news:
...
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

These things can really bug you!

Thanks for your guidance Ardus

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

HTH
--
AP


"John" a écrit dans le message de news:
...
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

Try something like:
=IF(B5="","",INDEX(PhoneNames,MATCH(TEXT(B5,"00000 00"),PhoneNumbers,0)))
assuming the PhoneNumbers are 7 digits (padded with leading zeros)
and the input is just numbers (wo leading zeros)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"John" wrote:
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