Lookup from a list

  • Thread starter Thread starter Rainyvanc
  • Start date Start date
R

Rainyvanc

I'm trying to find a function that will lookup a value
from a list and retrieve a value in the second column.

For example, List 1 has item codes

123456
233556
etc

I want to see if those items are in a sales report that
have item codes and sales numbers. If the item codes
match, then I want the formula to return the sales value
for the item.

For example, List 2 is

Item Sales$
123456 $500
123467 $700
345678 $800
233556 $1000

In the case of List 1, I want a function that will look at
List 2 and return the values of $500 and $1000 for each of
the two items.

I've tried Vlookup (i.e. vlookup(field, range, 2)) which
worked UNLESS THE ITEM ISN'T in List 2. The problem I'm
having is if the item isn't in List 2, the function is
returning the sales value of the next item on the list.
Is there a function that will only return the sales value
if the two items match exactly?

Thanks
 
Hi,

Vlookup is the function you want. Vlookup has an argument that you don't
seem to have noticed. This argument tells excel whether you want to look
for an exact match or an approximate one. If you specify TRUE or omit this
argument, excel will match the largest value less than your target value.
False will require an exact match. However, in the event that the item
isn't in the list, vlookup will return #N/A when an exact match is required
so you will need to test for this.

=if(ISNA(VLOOKUP(D2,A2:B5,2,FALSE)),"",VLOOKUP(D2,A2:B5,2,FALSE))

HTH

-Dave
 
Back
Top