Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how do I get lookup to work
Hi All,
I have a growing list of about 3000 8 digit phone numbers and I need to check if the 3rd and 4th digits of these numbers (eg 74) is equal to another number in a list (array) of about 15 numbers and if so to flag it. But I'm getting nowhere. I tried lookup (which will pick the number closest number - not look for an exact match), and Vlookup which just didn't seem to work. Can anyone point me is the right direction... any help and advice would really be appreciated. Thanks! -- Grant Turri-Petrie |
#2
|
|||
|
|||
Try =INDEX($E$1:$E$12,MATCH(MID(A2,3,2)*1,$E$1:$E$12,0 ),1) Where E1:E12 is your array of two digit numbers to check. A no-match will return #Value (if the number in A2 has too few digits) or #NA (if no match). Steve "Grant" wrote in message ... Hi All, I have a growing list of about 3000 8 digit phone numbers and I need to check if the 3rd and 4th digits of these numbers (eg 74) is equal to another number in a list (array) of about 15 numbers and if so to flag it. But I'm getting nowhere. I tried lookup (which will pick the number closest number - not look for an exact match), and Vlookup which just didn't seem to work. Can anyone point me is the right direction... any help and advice would really be appreciated. Thanks! -- Grant Turri-Petrie |
#3
|
|||
|
|||
maybe something like:
=IF(ISNUMBER(MATCH(MID(A1,3,2),Sheet2!$a$1:$A$15,0 )),"found it","missing") Grant wrote: Hi All, I have a growing list of about 3000 8 digit phone numbers and I need to check if the 3rd and 4th digits of these numbers (eg 74) is equal to another number in a list (array) of about 15 numbers and if so to flag it. But I'm getting nowhere. I tried lookup (which will pick the number closest number - not look for an exact match), and Vlookup which just didn't seem to work. Can anyone point me is the right direction... any help and advice would really be appreciated. Thanks! -- Grant Turri-Petrie -- Dave Peterson |
#4
|
|||
|
|||
Steve and Dave. Thanks for your suggestions... You have saved me hours of
work... Thanks again. "Grant" wrote: Hi All, I have a growing list of about 3000 8 digit phone numbers and I need to check if the 3rd and 4th digits of these numbers (eg 74) is equal to another number in a list (array) of about 15 numbers and if so to flag it. But I'm getting nowhere. I tried lookup (which will pick the number closest number - not look for an exact match), and Vlookup which just didn't seem to work. Can anyone point me is the right direction... any help and advice would really be appreciated. Thanks! -- Grant Turri-Petrie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
Change Path names in copied work book | Excel Worksheet Functions | |||
Lookup Doesn't Work | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |