Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup Function - Specific Values
How do I get a formula that does a standard LOOKUP, but returns an exact
match, rather than matching the nearest figure in the lookup table. EXAMPLE: I have a formula thus: =LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1:$B$100) However, if cell B5 contains say 1562 and the closest match in the lookup table is 1540, then it returns the result in column B for 1540. I would like it to return the words "No Match" if it can't find an exact match. Any ideas? Thanks. Steve. |
#2
|
|||
|
|||
Try
=IF(ISNA(LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1: $B$100,0),"No Match",LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1:$B $100,0)) Notice the extra 0 as the last argument in the Lookup function. This requires and exact match. If there is no match, the function returns a #NA error "Steve Elliott" wrote: How do I get a formula that does a standard LOOKUP, but returns an exact match, rather than matching the nearest figure in the lookup table. EXAMPLE: I have a formula thus: =LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1:$B$100) However, if cell B5 contains say 1562 and the closest match in the lookup table is 1540, then it returns the result in column B for 1540. I would like it to return the words "No Match" if it can't find an exact match. Any ideas? Thanks. Steve. |
#3
|
|||
|
|||
Thanks, although this formula returns a "too many arguments" error message.
I'm using Excel 97. Steve. "Duke Carey" wrote in message ... Try =IF(ISNA(LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1: $B$100,0),"No Match",LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1:$B $100,0)) Notice the extra 0 as the last argument in the Lookup function. This requires and exact match. If there is no match, the function returns a #NA error "Steve Elliott" wrote: How do I get a formula that does a standard LOOKUP, but returns an exact match, rather than matching the nearest figure in the lookup table. EXAMPLE: I have a formula thus: =LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1:$B$100) However, if cell B5 contains say 1562 and the closest match in the lookup table is 1540, then it returns the result in column B for 1540. I would like it to return the words "No Match" if it can't find an exact match. Any ideas? Thanks. Steve. |
#4
|
|||
|
|||
Use VLOOKUP instead...
=IF(ISNA(VLOOKUP(B5,Lookup!$A$1:$B$100,2,0)),"No Match",VLOOKUP(B5,Lookup!$A$1:$B$100,2,0)) Hope this helps! In article , "Steve Elliott" wrote: How do I get a formula that does a standard LOOKUP, but returns an exact match, rather than matching the nearest figure in the lookup table. EXAMPLE: I have a formula thus: =LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1:$B$100) However, if cell B5 contains say 1562 and the closest match in the lookup table is 1540, then it returns the result in column B for 1540. I would like it to return the words "No Match" if it can't find an exact match. Any ideas? Thanks. Steve. |
#5
|
|||
|
|||
Excel 97 has nothing to do with it. My bad reading is the culprit
Try this one instead: =IF(ISNA(VLOOKUP(B5,Lookup!$A$1:$B$100,2,0)),"No Match",VLOOKUP(B5,Lookup!$A$1:$B$100,2,0)) Mea culpa "Steve Elliott" wrote: Thanks, although this formula returns a "too many arguments" error message. I'm using Excel 97. Steve. "Duke Carey" wrote in message ... Try =IF(ISNA(LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1: $B$100,0),"No Match",LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1:$B $100,0)) Notice the extra 0 as the last argument in the Lookup function. This requires and exact match. If there is no match, the function returns a #NA error "Steve Elliott" wrote: How do I get a formula that does a standard LOOKUP, but returns an exact match, rather than matching the nearest figure in the lookup table. EXAMPLE: I have a formula thus: =LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1:$B$100) However, if cell B5 contains say 1562 and the closest match in the lookup table is 1540, then it returns the result in column B for 1540. I would like it to return the words "No Match" if it can't find an exact match. Any ideas? Thanks. Steve. |
#6
|
|||
|
|||
Cheers ! Works perfectly.
Thanks for your help. S. "Duke Carey" wrote in message ... Excel 97 has nothing to do with it. My bad reading is the culprit Try this one instead: =IF(ISNA(VLOOKUP(B5,Lookup!$A$1:$B$100,2,0)),"No Match",VLOOKUP(B5,Lookup!$A$1:$B$100,2,0)) Mea culpa "Steve Elliott" wrote: Thanks, although this formula returns a "too many arguments" error message. I'm using Excel 97. Steve. "Duke Carey" wrote in message ... Try =IF(ISNA(LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1: $B$100,0),"No Match",LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1:$B $100,0)) Notice the extra 0 as the last argument in the Lookup function. This requires and exact match. If there is no match, the function returns a #NA error "Steve Elliott" wrote: How do I get a formula that does a standard LOOKUP, but returns an exact match, rather than matching the nearest figure in the lookup table. EXAMPLE: I have a formula thus: =LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1:$B$100) However, if cell B5 contains say 1562 and the closest match in the lookup table is 1540, then it returns the result in column B for 1540. I would like it to return the words "No Match" if it can't find an exact match. Any ideas? Thanks. Steve. |
#7
|
|||
|
|||
Steve Elliott wrote:
How do I get a formula that does a standard LOOKUP, but returns an exact match, rather than matching the nearest figure in the lookup table. EXAMPLE: I have a formula thus: =LOOKUP(B5,Lookup!$A$1:$A$100,Lookup!$B$1:$B$100) However, if cell B5 contains say 1562 and the closest match in the lookup table is 1540, then it returns the result in column B for 1540. I would like it to return the words "No Match" if it can't find an exact match. Any ideas? Thanks. Steve. If Lookup!$A$1:$B$100 is sorted on its first column (looks like it is for you invoke a LOOKUP formula)... =IF(LOOKUP(B5,Lookup!$A$1:$A$100)=B5,LOOKUP(B5,Loo kup!$A$1:$B$100),"No Match") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Function Error | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
LOOKUP FUNCTION WITH SUMS VALUES | Excel Discussion (Misc queries) | |||
need to save values from a function before it changes | Excel Worksheet Functions |