LOOKUP Help

[Guest]

Hi

I have 3 columns of data. (approx 1,000 rows)

The 1st column has an identifier number (ie; PN22001-A in cell A1)
The 2nd Colum has a colour (ie; Mist Green in cell B1)
The 3rd colum has a stock number (ie; 4505-3456 in cell C1)

I would like to be able to have a lookup table in the 4th column that when
the idendifier number is typed in the next 2 adjacent cells returns the
colour and the stock number. Is this possible?

Any help would be much appreciated.

John

 

[Guest]

One way ..

Assuming inputs for the identifier number will be made in a 2-part form,
eg in E1: PN22001, in F1: A

Then we could place in D1:
=IF(OR(E1="",F1=""),"",INDEX(B:B,MATCH(E1&"-"&F1,A:A,0))&",
"&INDEX(C:C,MATCH(E1&"-"&F1,A:A,0)))

D1 will return the result from cols B & C as: Mist Green, 4505-3456

D1 can be copied down to return correspondingly
for other pairs of inputs in E2:F2, E3:F3, etc