I see that when using the lookup function, if the lookup value entered does
not match anything in the lookup vector, the result vector returns as the
next lowest value. Is there any way to change that so that if there is no
match you get a #VALUE! result instead, or something other than the next
lowest value?

Don't use LOOKUP(), use VLOOKUP() (or HLOOKUP()) instead. There you can
specify wheter or not the table is sorted, wheter an exact match should be
returned, etc

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Tom" wrote in message
...
I see that when using the lookup function, if the lookup value entered does
not match anything in the lookup vector, the result vector returns as the
next lowest value. Is there any way to change that so that if there is no
match you get a #VALUE! result instead, or something other than the next
lowest value?

Thank you,

But I still can't get it to work. I have a SKU's #'s in Column D and Product
Descriptions in Column E. I want to be able to enter a SKU # in column A and
have it return the value from column E, unless the number is not found, then
it would return N/A. How would I set this up? I'm unclear what the
differences are between look_value, table_array, col_index_number and range
lookup.

Thanks for your help

"Niek Otten" wrote:

Don't use LOOKUP(), use VLOOKUP() (or HLOOKUP()) instead. There you can
specify wheter or not the table is sorted, wheter an exact match should be
returned, etc

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Tom" wrote in message
...
I see that when using the lookup function, if the lookup value entered does
not match anything in the lookup vector, the result vector returns as the
next lowest value. Is there any way to change that so that if there is no
match you get a #VALUE! result instead, or something other than the next
lowest value?

Follow this excellent short tutorial:

http://www.contextures.com/xlFunctions02.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Tom" wrote in message
...
Thank you,

But I still can't get it to work. I have a SKU's #'s in Column D and
Product
Descriptions in Column E. I want to be able to enter a SKU # in column A
and
have it return the value from column E, unless the number is not found,
then
it would return N/A. How would I set this up? I'm unclear what the
differences are between look_value, table_array, col_index_number and
range
lookup.

Thanks for your help

"Niek Otten" wrote:

Don't use LOOKUP(), use VLOOKUP() (or HLOOKUP()) instead. There you can
specify wheter or not the table is sorted, wheter an exact match should
be
returned, etc

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Tom" wrote in message
...
I see that when using the lookup function, if the lookup value entered
does
not match anything in the lookup vector, the result vector returns as
the
next lowest value. Is there any way to change that so that if there is
no
match you get a #VALUE! result instead, or something other than the
next
lowest value?

Hi,

This is a feature of this function, which also implies that sort order is
important. You have a number of alternatives including but not limited to

VLOOKUP - you can set the last argument to FALSE which means exact match
MATCH with OFFSET - you can set the last argument of MATCH to 0 which mean
exact match
MATCH with INDEX

If this helps, please click the Yes button

Cheers,
Shane Devenshire
"Tom" wrote:

I see that when using the lookup function, if the lookup value entered does
not match anything in the lookup vector, the result vector returns as the
next lowest value. Is there any way to change that so that if there is no
match you get a #VALUE! result instead, or something other than the next
lowest value?