How do I do a two level Vlookup?

[molsansk]

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.

[Marcelo]

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:

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

[Toppers]

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
"molsansk" wrote:

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

[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 picks 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:D$100,2,0)

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

Hope this helps.

Pete

In
molsansk wrote:
> 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.

[molsansk]

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" wrote:

>
> 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 picks
> the first match, the 2nd picks the last......
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=574220
>
>

[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 the correct
value in the formula result but won't display it in the cell. Any clue why?

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

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