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.

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.

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.

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.

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.

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.

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