[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 dont 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)

On Jun 7, 3:46 pm, kawfeehaus
wrote:
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?

[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.


--
Biff
Microsoft Excel MVP


"kawfeehaus" wrote in message
...
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?

[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",VLOOKU P(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

"kawfeehaus" wrote in message
...
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?

[Dave The Favorite]

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

"L. Howard Kittle" wrote:

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",VLOOKU P(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

[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


"Dave The Favorite" wrote in
message ...
Works Perfectly! Put yourself in for a raise, Howard....and I'll approve
it.
Dave Stevens :-)

"L. Howard Kittle" wrote:

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",VLOOKU P(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