| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double Lookup
I am trying to do a double lookup. Not sure why I cant 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 Im not getting the expected results. Please help. Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Double Lookup
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" wrote: I am trying to do a double lookup. Not sure why I cant 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 Im not getting the expected results. Please help. Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Double Lookup
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" wrote: 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" wrote: I am trying to do a double lookup. Not sure why I cant 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 Im not getting the expected results. Please help. Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Double Lookup
That works! Thanks so much!
Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Luke M" wrote: 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" wrote: 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" wrote: I am trying to do a double lookup. Not sure why I cant 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 Im not getting the expected results. Please help. Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Double Lookup
Try the GETPIVOTDATA function, is is well documented in the help files
"ryguy7272" wrote: I am trying to do a double lookup. Not sure why I cant 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 Im not getting the expected results. Please help. Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Double Lookup
=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" wrote in message ... 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" wrote: 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" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Double Lookup | Excel Worksheet Functions | |||
| Variable Lookup/Double Lookup | Excel Worksheet Functions | |||
| Double LOOKUP? Help please... | Excel Worksheet Functions | |||
| double lookup | Excel Worksheet Functions | |||
| Double lookup | Excel Worksheet Functions | |||
Linear Mode
