Vlookup to change a column

C

Cube Farmer

Hello, I have a column of customers (A) and a column of Part #s (B).
I also have a short column of part #s (C). If a part in (B) is found in (C),
I want it to change to a totally different customer, say it is in (D). If it
doesn't match I want it to stay the same.
Thanks for looking.
Mike
 
T

Tom Hutchins

It sounds like you want to put a Vlookup in column A with a possible result
being the value that already was in column A. You can't have a value and a
formula in the same cell. But you could do this:

1. Insert a new column A (a new column to the left of A).
2. In A1, enter the following formula:
=IF(ISERROR(VLOOKUP(C1,D:E,2,FALSE)),B1,VLOOKUP(C1,D:E,2,FALSE))
3. Copy A1 down as far as needed.

Hope this helps,

Hutch
 
L

L. Howard Kittle

Hi Mike,

I believe I figured out your lookup but had to move the long list of
customers to the right of the long-list of part numbers.

=IF(ISNA(VLOOKUP(F15,D15:E19,2,0)),VLOOKUP(F15,B15:C24,2,0),VLOOKUP(F15,D15:E19,2,0))

Where F15 is the part number to look up.
D15:E19 is the short part# list and different customers.
B15:C24 is the long part# and customer list.

So, if the part# in F15 is found in the first lookup you get a different
customer from the short list.
If the part# in F15 is not found in the first lookup, it returns ISNA and
the lookup goes the long list.

I can send my sample work sheet if you want to help you figure it out.

HTH
Regards,
Howard
 
Top