How do I do a two level Vlookup?

[Guest]

I have been trying to search for the answer but can't quite find it. I am
trying to do what I can only think of to call a two level lookup. Basically
it requires to pieces of data to uniquely identify the record.

In the example below I will have a record that contains a business unit and
an account next to each other. I want to look back at another worksheet to
pull the amount.

Business Unit Account Amount
00001 9000 $5
00002 9000 $10
00002 9001 $2
00002 9002 $13
00003 9002 $8
00003 9003 $21


So for example if I had another worksheet set up with the following.

00002 9002

how do i pull the value $13? For simplicity sake we can assume the upper
right hand value of '00001' is in cell A1.

Thanks to anyone with any advice.

 

[Guest]

Hi Molsansk,

=sumproduct(--(a2:a1000="00002")*(b2:b1000="9002");(c2:c1000)

I have use " " assuming that the BU and account are formated as text, is
it's number just use:
=sumproduct(--(a2:a1000=00002)*(b2:b1000=9002);(c2:c1000)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"molsansk" escreveu:

 

[Guest]

Hi Molsansk,

=sumproduct(--(a2:a1000="00002")*(b2:b1000="9002");(c2:c1000)

I have use " " assuming that the BU and account are formated as text, is
it's number just use:
=sumproduct(--(a2:a1000=00002)*(b2:b1000=9002);(c2:c1000)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"molsansk" escreveu:

 

[Guest]

In sheet2 :

=INDEX(Sheet1!C1:C100,MATCH(1,(Sheet1!A1:A100="00002")*(Sheet1!B1:B100="9002"),0),1)

enter with CTRL+SHIFT+ENTER (array formula)

You can replace literals with cells containing their values.

HTH

 

[Guest]

In sheet2 :

=INDEX(Sheet1!C1:C100,MATCH(1,(Sheet1!A1:A100="00002")*(Sheet1!B1:B100="9002"),0),1)

enter with CTRL+SHIFT+ENTER (array formula)

You can replace literals with cells containing their values.

HTH

 

[daddylonglegs]

You could try

=INDEX(C1:C10,MATCH(1,(A1:A10=H1)*(B1:B10=H2),0))

confirmed with CTRL+SHIFT+ENTER

where H1 contains 00002 and H2 contains 9002

another option which only requires ENTER

=LOOKUP(2,1/((A1:A10=H1)*(B1:B10=H2)),C1:C10)

If the combination occurs more than once then the first of these pick
the first match, the 2nd picks the last.....

 

[daddylonglegs]

You could try

=INDEX(C1:C10,MATCH(1,(A1:A10=H1)*(B1:B10=H2),0))

confirmed with CTRL+SHIFT+ENTER

where H1 contains 00002 and H2 contains 9002

another option which only requires ENTER

=LOOKUP(2,1/((A1:A10=H1)*(B1:B10=H2)),C1:C10)

If the combination occurs more than once then the first of these pick
the first match, the 2nd picks the last.....

 

[Pete_UK]

I think the easiest way is to "join" the two uniques together in your
reference table. In your example, insert a new column C (to the left of
Amount) and add this formula to C2 (you have headings, so I assume
these are on row 1):

=A2&B2

Copy the formula down by double-clicking the fill handle (the small
black square in the bottom right corner of the cursor, with C2
selected).

Then in your second worksheet, cell C2, you should enter this formula:

=VLOOKUP(A2&B2,Sheet1!C$2$100,2,0)

Adjust the cell references and sheet name in the second parameter to
suit your data.

Hope this helps.

Pete

 

[Pete_UK]

I think the easiest way is to "join" the two uniques together in your
reference table. In your example, insert a new column C (to the left of
Amount) and add this formula to C2 (you have headings, so I assume
these are on row 1):

=A2&B2

Copy the formula down by double-clicking the fill handle (the small
black square in the bottom right corner of the cursor, with C2
selected).

Then in your second worksheet, cell C2, you should enter this formula:

=VLOOKUP(A2&B2,Sheet1!C$2$100,2,0)

Adjust the cell references and sheet name in the second parameter to
suit your data.

Hope this helps.

Pete

 

[Guest]

Thanks.

The second one you gave me, =LOOKUP(2,1/((A1:A10=H1)*(B1:B10=H2)),C1:C10)
appears to be working.

The first one, =INDEX(C1:C10,MATCH(1,(A1:A10=H1)*(B1:B10=H2),0)), returns
#VALUE, but when I bring up the function dialog box, it shows the the correct
value in the formula result but won't display it in the cell. Any clue why?

 

[daddylonglegs]

The first one, =INDEX(C1:C10,MATCH(1,(A1:A10=H1)*(B1:B10=H2),0))
returns
#VALUE, but when I bring up the function dialog box, it shows the th
correct
value in the formula result but won't display it in the cell. Any clu
why?

This formula needs to be "array entered" as specified. To do thi
select cell with formula, hit F2 then whilst holding down CTRL+SHIF
keys hit ENTER.

Some people prefer to avoid these type of formulas for obvious reason
which is why I also suggested the other approach, although, as I say
they can give different results in some circumstances....