Lookup Question

[Tom]

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?

 

[Niek Otten]

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

 

[Cheers,]

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]

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]

Follow this excellent short tutorial:

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