Double Vlookup???

  • Thread starter Thread starter Tetsuya Oguma
  • Start date Start date
T

Tetsuya Oguma

Hi all,

I am stumbled on this one.

I have two tables and Table 1 has all the data to look up.
Then in Table 2 I'd like to extract Value which is
associated with the Name and Type. In the following
example, I have named "test1" containing two rows in Table
1 ("Adam 1 50" and "Adam 2 100"). In the cell
currently showing "???", I'd like to have Value of 50 in
Table 2.

Table 1 Table 2
Name Type Value Name Type Value
Adam 1 50 Adam 1 ???
Adam 2 100

I can obviously INDIVIDUALLY check if a row's Name is Adam
and Type is 1 respectively. But how can I combine the both
checkings in a single formula and in the end get Value of
50 in the above example???

Thanks for your time. I am using Excel 97 SR-2.
 
In this specific example, if Table 1 is in columns A:C and Table 2 in D:F:

=INDEX(C1:C2,MATCH(D1&E1,A1:A2&B1:B2,0))

entered as an array formula with <Ctrl> <Shift> <Enter>.
 
Thanks for your reply. However, entering multi columns in
look_up value (the first parameter in the MATCH function)
doesn't seem to work... I did enter it as array formula.

Cheers,
 
One way:

Suppose Table 1 is in Sheet1,
cols A - C, row2 down
(Cols A - C: Name-Type-Value)

put in D2: =TRIM(A2&B2)
copy down col D

(It's assumed there are no *duplicate* "Name-Type" combos,
viz. Adam1, Adam2 are all uniques)

If Table 2 is in, say Sheet2,
cols A - C, row2 down
(Cols A - C: Name-Type-Value)

Put in C2
: =OFFSET(Sheet1!$A$1,MATCH(TRIM(A2&B2),Sheet1!D:D,0)-1,2,1,1)

Copy down col C

Col C should return what is wanted.
 
Let A1:C3 on Sheet1 house Table 1.

In D1 enter: Concat

In D2 enter & copy down:

=A2&CHAR(127)&B2

Let A2:B2 on Sheet2 house the lookup values of interest (Adam and 1).

In C2 enter:

=INDEX(Sheet1!$C$2:$C$3,MATCH(A2&CHAR(127)&B2,Sheet1!$D$2:$D$3,0))
 
Worked perfectly for me in Excel 2003 ... I did test it before posting.

0 A B C D E F
1 Adam 1 50 Adam 2 30
2 Adam 2 30

Formula in F1:

{=INDEX(C1:C2,MATCH(D1&E1,A1:A2&B1:B2,0))}
 
Back
Top