Vlookup and IF function problem

[yuste]

Hi,

I would like to do a kind of double Vlookup.
I have 2 different worksheets with following information:

Worksheet1 is the master file with all modells: 3 columns
A B C
B segment Honda Civic B
J segment Honda Civic Coupé
D segment Toyota Avensis D
...

Worksheet2 is the data file: 3 columns, of which 2 are filled

A B
C
B segment Honda Civic ...

J segment Honda Civic ...
D segment Toyota Avensis ...
...

I would like to get in the C column of worksheet 2 the results you fin
in column C of worksheet 1.

So I believe it should look up: if a car belongs to the B segment an
it is a Honda Civic than it will be a "B" car according to the looku
in worksheet 1.

Can somebody help me with this formula?
Thanks a lot in advance,

Steve

 

[Domenic]

Hi Steve,

Try,

=INDEX(Sheet1!$C$1:$C$3,MATCH(1,(Sheet1!$A$1:$A$3=Sheet2!A1)*(Sheet1!$B$1:$B$3=Sheet2!B1),0))

Adjust the range to suit your data, enter the formula usin
CTRL+SHIFT+ENTER, and copy down.

Hope this helps!

 

[jeff]

Hi,

In my mind, the easiest way would be to add a helper
column in both sheets (or at least sheet1) which is
a combination of col A + Col B, then one simple vlookup
will still work great. You can always hide the helper.

hth.
jeff

-----Original Message-----
Hi,

I would like to do a kind of double Vlookup.
I have 2 different worksheets with following information:

Worksheet1 is the master file with all modells: 3 columns
A B C
B segment Honda Civic B
J segment Honda Civic Coupé
D segment Toyota Avensis D
...

Worksheet2 is the data file: 3 columns, of which 2 are filled

A

B

C
B segment Honda

Civic ...

J segment Honda

Civic ...

 

[Soo Cheon Jheong]

Hi,

Use one of these formulas in Sheet2!C1:

=INDIRECT("Sheet1!C"&MIN(IF((Sheet1!$A$1:$A$9=A1)
*(Sheet1!$B$1:$B$9=B1),ROW(Sheet1!$C$1:$C$9),"")))

or

=INDIRECT("Sheet1!C"&MAX((Sheet1!$A$1:$A$9=A1)
*(Sheet1!$B$1:$B$9=B1)*ROW(Sheet1!$C$1:$C$9)))

or

=INDIRECT("Sheet1!C"&MAX((Sheet1!$A$1:$A$9&
Sheet1!$B$1:$B$9=A1&B1)*ROW(Sheet1!$C$1:$C$9)))

then drag and fill down.


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--

 

[yuste]

Thanks a lot guys for the big help. Very much appreciated!!! It work
out