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.

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.

In sheet2 :

=INDEX(Sheet1!C1:C100,MATCH(1,(Sheet1!A1:A100="000 02")*(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.

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

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.

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

molsansk Wrote:

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=574220