Lookup Function - Specific Values

[Steve Elliott]

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.

 

[Guest]

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]

Thanks, although this formula returns a "too many arguments" error message.
I'm using Excel 97.

Steve.

 

[Domenic]

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!

 

[Guest]

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]

Cheers ! Works perfectly.

Thanks for your help.

S.

 

[Aladin Akyurek]

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,Lookup!$A$1:$B$100),"No
Match")