Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Steve Elliott
 
Posts: n/a
Default 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   Report Post  
Duke Carey
 
Posts: n/a
Default

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   Report Post  
Steve Elliott
 
Posts: n/a
Default

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   Report Post  
Domenic
 
Posts: n/a
Default

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   Report Post  
Duke Carey
 
Posts: n/a
Default

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   Report Post  
Steve Elliott
 
Posts: n/a
Default

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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup Function Error Jacinthe Excel Worksheet Functions 2 March 10th 05 08:37 AM
Lookup Function Problem seve Excel Discussion (Misc queries) 9 February 28th 05 03:46 AM
Lookup function w/Text and Year Josh O. Excel Worksheet Functions 1 February 13th 05 12:27 AM
LOOKUP FUNCTION WITH SUMS VALUES Jamesy Excel Discussion (Misc queries) 3 January 10th 05 04:03 PM
need to save values from a function before it changes Ron Excel Worksheet Functions 1 October 29th 04 06:29 AM


All times are GMT +1. The time now is 11:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"