Find exact value using LOOKUP

[kawfeehaus]

I am attempting to use the LOOKUP function to give a numerical value for a
corresponding text. The problem is if the text is missing in the vector chart
it just defaults to the value for the text just one cell above where the text
would be (in abc order) if it were actually included in the chart.

For example:

=LOOKUP(B1,$AA$1:$AB$84)

And my chart is
AA AB (cell columns)
Apple 1000
Cat 2000
Dog 3000
Zebra 4000

Looking at my formula, if the word apricot is in the cell B1 then the value
will come out to 1000 because Apple is the highest value before where apricot
would have been (thinking in ABC order here). The problem is apricot is
really value 1500 but was left off of the chart.

I don’t want the formula to do that, I want it to find the exact match or
give me some sort of error in the cell with the formula
[=LOOKUP(B1,$AA$1:$AB$84)] so that I know I need to add it to the vector
list.
Is there any way to make it look for the *exact* text as opposed to a range
per se?

 

[Tim879]

Try using the vlookup function as below...

=VLOOKUP(B1,$AA$1:$AB$84,2,false)

 

[ward376]

Populate the fourth argument of vlookup with false. If the exact value
isn't found, you'll get an #N/A.

Cliff Edwards

 

[T. Valko]

Use VLOOKUP and set the 4th argument to 0 or FALSE:

=VLOOKUP(B1,$AA$1:$AB$84,2,0)

In this case *exact* excludes case:

APPLE will be an "exact" match of apple.

If an "exact" match is not found the formula will return #N/A.

 

[kawfeehaus]

Tim879 and ward376

Thanks! It works! I get #N/A when it is not present in the chart. This is a
big help!

K

 

[ward376]

You're welcome.

Cliff Edwards

 

[L. Howard Kittle]

In addition to the other solutions, if you don't want the #N/A to show in
your return cell, give this a try, adjust ranges to suit your sheet, as well
as what you want to be returned. In these cases its "nothing", "the value
zero" or "anything yoy want".

=IF(ISNA(VLOOKUP(C1,$A$1:$B$4,2,FALSE)),"",VLOOKUP(C1,$A$1:$B$4,2,FALSE))

=IF(ISNA(VLOOKUP(C1,$A$1:$B$4,2,FALSE)),"0",VLOOKUP(C1,$A$1:$B$4,2,FALSE))

=IF(ISNA(VLOOKUP(C1,$A$1:$B$4,2,FALSE)),"Any thing you want
here",VLOOKUP(C1,$A$1:$B$4,2,FALSE))

HTH
Regards,
Howard

 

[Dave The Favorite]

Works Perfectly! Put yourself in for a raise, Howard....and I'll approve it.
Dave Stevens

 

[L. Howard Kittle]

Hey thanks, Dave S. Mailed in for the raise and I'm positive the
check-is-in-the-mail!!! (Discounted for misspelling the word "you" as
"yoy")

Thanks for the feedback.
Howard