Double Lookup

R

ryguy7272

I am trying to do a double lookup. Not sure why I can’t get it working. I
tried both functions below:

=INDEX('[exp 05 09.xls]PivotSheet'!$D:$D,MATCH('[exp 05
09.xls]PivotSheet'!$A:$A=C1,MATCH('[exp 05 09.xls]PivotSheet'!$C:$C=A12,0)))
This was CSE-Entered
Returns #NUM!

=INDEX('[exp 05 09.xls]PivotSheet'!$D:$D,MATCH(C1,'[exp 05
09.xls]PivotSheet'!$A:$A,MATCH(A12,'[exp 05 09.xls]PivotSheet'!$C:$C,0)))
Returns 0


I know the values are in those sheets, but I’m not getting the expected
results.

Please help.

Thanks,
Ryan---
 
L

Luke M

You are not using either INDEX of MATCH correctly! I'm not exactly sure what
type of result you are expecting, but you can not use a single column in
Index and then use 2 other variables.

Also, why do your MATCH functions contain "=" operators? The structure for
MATCH is:
=MATCH(LookupValue,LookupArray,MatchType)

If you could provide more detail about what it is exactly you're trying to
do, we might be able to provide additional help.
 
L

Luke M

My apologies. On re-reading your post, I see that your first formula is
CSE-entered, thus explaining the "=" operator.

If column D is a number:
=SUMPRODUCT(('[exp 05 09.xls]PivotSheet'!D2:D2000),--('[exp 05
09.xls]PivotSheet'!A2:A2000=C1),--('[exp 05 09.xls]PivotSheet'!C2:C2000=A12))

If column D is text:
=INDEX('[exp 05 09.xls]PivotSheet'!D:D,SUMPRODUCT(MAX(ROW('[exp 05
09.xls]PivotSheet'!D2:D2000)*('[exp 05 09.xls]PivotSheet'!A2:A2000=C1)*('[exp
05 09.xls]PivotSheet'!C2:C2000=A12))))

Note that the arguements within SUMPRODUCT can't callout entire columns,
unless using XL 2007.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


Luke M said:
You are not using either INDEX of MATCH correctly! I'm not exactly sure what
type of result you are expecting, but you can not use a single column in
Index and then use 2 other variables.

Also, why do your MATCH functions contain "=" operators? The structure for
MATCH is:
=MATCH(LookupValue,LookupArray,MatchType)

If you could provide more detail about what it is exactly you're trying to
do, we might be able to provide additional help.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


ryguy7272 said:
I am trying to do a double lookup. Not sure why I can’t get it working. I
tried both functions below:

=INDEX('[exp 05 09.xls]PivotSheet'!$D:$D,MATCH('[exp 05
09.xls]PivotSheet'!$A:$A=C1,MATCH('[exp 05 09.xls]PivotSheet'!$C:$C=A12,0)))
This was CSE-Entered
Returns #NUM!

=INDEX('[exp 05 09.xls]PivotSheet'!$D:$D,MATCH(C1,'[exp 05
09.xls]PivotSheet'!$A:$A,MATCH(A12,'[exp 05 09.xls]PivotSheet'!$C:$C,0)))
Returns 0


I know the values are in those sheets, but I’m not getting the expected
results.

Please help.

Thanks,
Ryan---
Click to expand...
Click to expand...
 
R

ryguy7272

That works! Thanks so much!
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Luke M said:
My apologies. On re-reading your post, I see that your first formula is
CSE-entered, thus explaining the "=" operator.

If column D is a number:
=SUMPRODUCT(('[exp 05 09.xls]PivotSheet'!D2:D2000),--('[exp 05
09.xls]PivotSheet'!A2:A2000=C1),--('[exp 05 09.xls]PivotSheet'!C2:C2000=A12))

If column D is text:
=INDEX('[exp 05 09.xls]PivotSheet'!D:D,SUMPRODUCT(MAX(ROW('[exp 05
09.xls]PivotSheet'!D2:D2000)*('[exp 05 09.xls]PivotSheet'!A2:A2000=C1)*('[exp
05 09.xls]PivotSheet'!C2:C2000=A12))))

Note that the arguements within SUMPRODUCT can't callout entire columns,
unless using XL 2007.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


Luke M said:
You are not using either INDEX of MATCH correctly! I'm not exactly sure what
type of result you are expecting, but you can not use a single column in
Index and then use 2 other variables.

Also, why do your MATCH functions contain "=" operators? The structure for
MATCH is:
=MATCH(LookupValue,LookupArray,MatchType)

If you could provide more detail about what it is exactly you're trying to
do, we might be able to provide additional help.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


ryguy7272 said:
I am trying to do a double lookup. Not sure why I can’t get it working. I
tried both functions below:

=INDEX('[exp 05 09.xls]PivotSheet'!$D:$D,MATCH('[exp 05
09.xls]PivotSheet'!$A:$A=C1,MATCH('[exp 05 09.xls]PivotSheet'!$C:$C=A12,0)))
This was CSE-Entered
Returns #NUM!

=INDEX('[exp 05 09.xls]PivotSheet'!$D:$D,MATCH(C1,'[exp 05
09.xls]PivotSheet'!$A:$A,MATCH(A12,'[exp 05 09.xls]PivotSheet'!$C:$C,0)))
Returns 0


I know the values are in those sheets, but I’m not getting the expected
results.

Please help.

Thanks,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
T

T. Valko

=INDEX('[exp 05 09.xls]PivotSheet'!D:D,SUMPRODUCT(MAX(ROW('[exp 05
09.xls]PivotSheet'!D2:D2000)*('[exp 05
09.xls]PivotSheet'!A2:A2000=C1)*('[exp
05 09.xls]PivotSheet'!C2:C2000=A12))))

That could also be written as an array formula** :

=INDEX('[exp 05 09.xls]PivotSheet'!D2:D2000,MATCH(1,('[exp 05
09.xls]PivotSheet'!A2:A2000=C1)*('[exp
05 09.xls]PivotSheet'!C2:C2000=A12),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


Luke M said:
My apologies. On re-reading your post, I see that your first formula is
CSE-entered, thus explaining the "=" operator.

If column D is a number:
=SUMPRODUCT(('[exp 05 09.xls]PivotSheet'!D2:D2000),--('[exp 05
09.xls]PivotSheet'!A2:A2000=C1),--('[exp 05
09.xls]PivotSheet'!C2:C2000=A12))

If column D is text:
=INDEX('[exp 05 09.xls]PivotSheet'!D:D,SUMPRODUCT(MAX(ROW('[exp 05
09.xls]PivotSheet'!D2:D2000)*('[exp 05
09.xls]PivotSheet'!A2:A2000=C1)*('[exp
05 09.xls]PivotSheet'!C2:C2000=A12))))

Note that the arguements within SUMPRODUCT can't callout entire columns,
unless using XL 2007.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


Luke M said:
You are not using either INDEX of MATCH correctly! I'm not exactly sure
what
type of result you are expecting, but you can not use a single column in
Index and then use 2 other variables.

Also, why do your MATCH functions contain "=" operators? The structure
for
MATCH is:
=MATCH(LookupValue,LookupArray,MatchType)

If you could provide more detail about what it is exactly you're trying
to
do, we might be able to provide additional help.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


ryguy7272 said:
I am trying to do a double lookup. Not sure why I can't get it
working. I
tried both functions below:

=INDEX('[exp 05 09.xls]PivotSheet'!$D:$D,MATCH('[exp 05
09.xls]PivotSheet'!$A:$A=C1,MATCH('[exp 05
09.xls]PivotSheet'!$C:$C=A12,0)))
This was CSE-Entered
Returns #NUM!

=INDEX('[exp 05 09.xls]PivotSheet'!$D:$D,MATCH(C1,'[exp 05
09.xls]PivotSheet'!$A:$A,MATCH(A12,'[exp 05
09.xls]PivotSheet'!$C:$C,0)))
Returns 0


I know the values are in those sheets, but I'm not getting the
expected
results.

Please help.

Thanks,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Lookup Wizard 4
Sumproduct not working 3
IF ARRAY with multiple conditions. 1
How to Shorten a repetitive formula to include more references. 7
Lookup next item with dates 4
How to Use two search criteria in one array. 2
Returning a Date with Multiple Criteria Lookup Values 0
excel formulas 1

Top