Trying to do a Lookup

[Heather C]

I'm trying to match a UPC # to an Item number on an invoice. I put the
formula in and it bring up thewrong item number. I'm not quite sure how to
fix this. The formula I'm using is
=IF (A20>0, LOOKUP(C20,Sheet1!A:A,Sheet1!B:B),"")

PLease HELP

 

[Mike H]

Heather,

I assume you have a reson for only executuing your lookup if A20> 0 because
it may be a bit unusual to check a value in your lookup vector but it's not
affecting the formula. In fact it works perfectly for me so can you expand
how it is going wrong.

Mike

 

[PCLIVE]

Your UPC #s would have to be in ascending order for lookup to work. Instead
try using a MATCH function along with INDIRECT:

=INDIRECT("Sheet1!B" & MATCH(C20,Sheet1!A:A,0))

HTH,
Paul

 

[Mike H]

Sorry an elderly moment your formula isn't on sheet 1 so your not checking a
value in your lookup vector. Last comment remains true, your formula works
perfectly for me so what is your error?

Mike

 

[T. Valko]

Try this:

=IF(A20>0,VLOOKUP(C20,Sheet1!A:B,2,0),"")

 

[Heather C]

When the program look up the UPC number it is returning the worng item and I
don't know why?