Vlookup and IF function problem

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 find
in column C of worksheet 1.

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

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

Steven


---
Message posted from http://www.ExcelForum.com/

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 using
CTRL+SHIFT+ENTER, and copy down.

Hope this helps!

yuste wrote:
*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
find in column C of worksheet 1.

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

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

Steven *


---
Message posted from http://www.ExcelForum.com/

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=E9
D segment Toyota Avensis =20
D =20
...

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

A =20
B =20
C =20
B segment Honda=20
Civic ... =20

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

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

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

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

Steven


---
Message posted from http://www.ExcelForum.com/

.

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
_ _
^¢¯^
--

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


---
Message posted from http://www.ExcelForum.com/