Vlookup and IF function problem

Y

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
 
D

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!
 
J

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 ...
 
S

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top