Formatting a lookup

[Heather C]

I have an invoice set up where it looks up the item number off of the upc,
however some of the upc's on the products don't match the ones in the
computer. Right now excell brings up the closest match to the upc. I would
like it if thier was a way that it would bring up nothing or an error if the
upc doesn't match exactally. The formula i'm using is:
=IF(A20>0, LOOKUP(C20,UPC!H:H,UPC!B:B),"")

 

[ryguy7272]

Try this:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Look at the last argument in the function; range_lookup
Range_lookup A logical value that specifies whether you want VLOOKUP to
find an exact match or an approximate match:

If TRUE or omitted, an exact or approximate match is returned. If an exact
match is not found, the next largest value that is less than lookup_value is
returned.
The values in the first column of table_array must be placed in ascending
sort order; otherwise, VLOOKUP may not give the correct value. You can put
the values in ascending order by choosing the Sort command from the Data menu
and selecting Ascending. For more information, see Default sort orders.

If FALSE, VLOOKUP will only find an exact match. In this case, the values in
the first column of table_array do not need to be sorted. If there are two or
more values in the first column of table_array that match the lookup_value,
the first value found is used. If an exact match is not found, the error
value #N/A is returned.

Does that work for you?
Ryan--

 

[Heather C]

I Still cannot get it to work

Try this:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Look at the last argument in the function; range_lookup
Range_lookup A logical value that specifies whether you want VLOOKUP to
find an exact match or an approximate match:

If TRUE or omitted, an exact or approximate match is returned. If an exact
match is not found, the next largest value that is less than lookup_value is
returned.
The values in the first column of table_array must be placed in ascending
sort order; otherwise, VLOOKUP may not give the correct value. You can put
the values in ascending order by choosing the Sort command from the Data menu
and selecting Ascending. For more information, see Default sort orders.

If FALSE, VLOOKUP will only find an exact match. In this case, the values in
the first column of table_array do not need to be sorted. If there are two or
more values in the first column of table_array that match the lookup_value,
the first value found is used. If an exact match is not found, the error
value #N/A is returned.

Does that work for you?
Ryan--

 

[ryguy7272]

If you send it to me I will try to get it working and send it back to you.
(e-mail address removed)

Remove the xxx part (it's only to confuse spammers, not to confuse you).

Ryan---

 

[Dave Peterson]

So you match C20 against UPC!H:H and return the value in column B?

=if(a20>0,index(upc!b:b,match(c20,upc!h:h,0),"")

 

[Dave]

Hi Heather,
Try this small modification to your formula:
=IF(A20>0, LOOKUP(C20,UPC!H:H,UPC!B:B,0),"")
Regards - Dave.