If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula with multiple variables
I have two reports pulled into a workbook. I am trying to match three
columns of data and bring back the results from a 4th column. I am using the following formula, but receive "0" in every field. Any help? =SUMPRODUCT(--('BO Data'!$E$2:$E$65533='SAP BW Data'!D2),--('BO Data'!$F$2:$F$65533='SAP BW Data'!B2),--('BO Data'!$G$2:$G$65533='SAP BW Data'!A2),('BO Data'!$D$2:$D$65533)) |
#2
|
|||
|
|||
Formula with multiple variables
If some of the values in the BO Data sheet are text values, then you
need to ensure that there are no leading or trailing (or multiple) spaces. If you think they are numbers, you should check that they are not text values that happen to look like numbers, and the same applies to the cells in the SAP BW Data sheet. Your formula looks fine (although, do you really need to check through 65k rows?), so clearly you have one or more columns where there is no exact match. Hope this helps. Pete On Jul 30, 11:29*pm, Sam wrote: I have two reports pulled into a workbook. *I am trying to match three columns of data and bring back the results from a 4th column. *I am using the following formula, but receive "0" in every field. * Any help? =SUMPRODUCT(--('BO Data'!$E$2:$E$65533='SAP BW Data'!D2),--('BO Data'!$F$2:$F$65533='SAP BW Data'!B2),--('BO Data'!$G$2:$G$65533='SAP BW Data'!A2),('BO Data'!$D$2:$D$65533)) |
#3
|
|||
|
|||
Formula with multiple variables
2 usual possibilities behind the failure
a. the matching's off due to extraneous white spaces b. the matching's ok, but the col to sum contains text numbers a. Use TRIM on all matches eg: instead of: --('BO Data'!$E$2:$E$65533='SAP BW Data'!D2) use: --(TRIM('BO Data'!$E$2:$E$65533)=TRIM('SAP BW Data'!D2)) b. Try coercing the sum col using an arithmetic op, say: +0, viz: 'BO Data'!$D$2:$D$65533+0 -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- "Sam" wrote: I have two reports pulled into a workbook. I am trying to match three columns of data and bring back the results from a 4th column. I am using the following formula, but receive "0" in every field. Any help? =SUMPRODUCT(--('BO Data'!$E$2:$E$65533='SAP BW Data'!D2),--('BO Data'!$F$2:$F$65533='SAP BW Data'!B2),--('BO Data'!$G$2:$G$65533='SAP BW Data'!A2),('BO Data'!$D$2:$D$65533)) |
#4
|
|||
|
|||
Formula with multiple variables
All the columns are "General", not "text". I tried using TRIM, but then I
received the error message #NAME? I tried +0 and that didn't work either. I am a little hesitant with +0 as the field is not a sum. It is a code reference. The fields in the formula are Doc #, Fiscal Year, and Company Code. I am trying to return a 2 digit Code. Any other ideas? "Max" wrote: 2 usual possibilities behind the failure a. the matching's off due to extraneous white spaces b. the matching's ok, but the col to sum contains text numbers a. Use TRIM on all matches eg: instead of: --('BO Data'!$E$2:$E$65533='SAP BW Data'!D2) use: --(TRIM('BO Data'!$E$2:$E$65533)=TRIM('SAP BW Data'!D2)) b. Try coercing the sum col using an arithmetic op, say: +0, viz: 'BO Data'!$D$2:$D$65533+0 -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- "Sam" wrote: I have two reports pulled into a workbook. I am trying to match three columns of data and bring back the results from a 4th column. I am using the following formula, but receive "0" in every field. Any help? =SUMPRODUCT(--('BO Data'!$E$2:$E$65533='SAP BW Data'!D2),--('BO Data'!$F$2:$F$65533='SAP BW Data'!B2),--('BO Data'!$G$2:$G$65533='SAP BW Data'!A2),('BO Data'!$D$2:$D$65533)) |
#5
|
|||
|
|||
Formula with multiple variables
Ah, I see. Sumproduct works only for numbers as the end return. You could use
an array-entered multi-criteria index/match to do the job. Since your range is huge, suggest you switch the book's calc mode to Manual first Then try this (it embodies the TRIM suggested for more robust matching), array-entered**, ie press CTRL+SHIFT+ENTER to confirm the formula: =INDEX('BO Data'!$D$2:$D$65533,MATCH(1,(TRIM('BO Data'!$E$2:$E$65533)=TRIM('SAP BW Data'!D2))*(TRIM('BO Data'!$F$2:$F$65533)=TRIM('SAP BW Data'!B2))*(TRIM('BO Data'!$G$2:$G$65533)=TRIM('SAP BW Data'!A2)),0)) (Copy the formula direct from the post, paste into the formula bar. Don't re-type.) **Visually check that the formula is wrapped by curly braces: { } after you press CTRL+SHIFT+ENTER to array-enter the formula in the cell (look in that cell's formula bar). If you don't see the curlies, that means you didn't array-enter it properly. Click inside the formula bar, re-do the CTRL+SHIFT+ENTER. And if the above works for you (it should), press the "Yes" button below from where you're reading this -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- "Sam" wrote: All the columns are "General", not "text". I tried using TRIM, but then I received the error message #NAME? I tried +0 and that didn't work either. I am a little hesitant with +0 as the field is not a sum. It is a code reference. The fields in the formula are Doc #, Fiscal Year, and Company Code. I am trying to return a 2 digit Code. Any other ideas? |
#6
|
|||
|
|||
Formula with multiple variables
I think we are getting closer. I followed your instructions. Now it returns
#N/A. Any more ideas? "Max" wrote: Ah, I see. Sumproduct works only for numbers as the end return. You could use an array-entered multi-criteria index/match to do the job. Since your range is huge, suggest you switch the book's calc mode to Manual first Then try this (it embodies the TRIM suggested for more robust matching), array-entered**, ie press CTRL+SHIFT+ENTER to confirm the formula: =INDEX('BO Data'!$D$2:$D$65533,MATCH(1,(TRIM('BO Data'!$E$2:$E$65533)=TRIM('SAP BW Data'!D2))*(TRIM('BO Data'!$F$2:$F$65533)=TRIM('SAP BW Data'!B2))*(TRIM('BO Data'!$G$2:$G$65533)=TRIM('SAP BW Data'!A2)),0)) (Copy the formula direct from the post, paste into the formula bar. Don't re-type.) **Visually check that the formula is wrapped by curly braces: { } after you press CTRL+SHIFT+ENTER to array-enter the formula in the cell (look in that cell's formula bar). If you don't see the curlies, that means you didn't array-enter it properly. Click inside the formula bar, re-do the CTRL+SHIFT+ENTER. And if the above works for you (it should), press the "Yes" button below from where you're reading this -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- "Sam" wrote: All the columns are "General", not "text". I tried using TRIM, but then I received the error message #NAME? I tried +0 and that didn't work either. I am a little hesitant with +0 as the field is not a sum. It is a code reference. The fields in the formula are Doc #, Fiscal Year, and Company Code. I am trying to return a 2 digit Code. Any other ideas? |
#7
|
|||
|
|||
Formula with multiple variables
"Sam" wrote:
I think we are getting closer. I followed your instructions. Now it returns #N/A. Darn, it should have worked. Unless there's really no match found for the 3 lookup values specified. Maybe re-check your *array-entering* of the formula? Did you see the curlies in the formula bar, after you confirmed the array-entry? It's quite common for us to rush through the CSE confirmation in a bid to get the formula working. If the formula is not correctly array-entered, it'll just return #N/A despite there being an obvious match. Post back your findings .. -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- |
#8
|
|||
|
|||
Formula with multiple variables
Yes I verified I had the curlies. I even changed the data on the first row
to make sure all the data was the same in both files. However, I have a new twist with the request that came up today. Same workbook and cell definition as previous posting: If Tab "DO Data" column E matches tab "SAP BW Data" column "D" and "DO Data" column F matches tab "SAP BW Data" column B and "DO Data" column G matches tab "SAP BW Data column A and "DO Data" column D equals 8 then return "DO Data" column C How will this change the formula you sent previously? Thanks. "Max" wrote: "Sam" wrote: I think we are getting closer. I followed your instructions. Now it returns #N/A. Darn, it should have worked. Unless there's really no match found for the 3 lookup values specified. Maybe re-check your *array-entering* of the formula? Did you see the curlies in the formula bar, after you confirmed the array-entry? It's quite common for us to rush through the CSE confirmation in a bid to get the formula working. If the formula is not correctly array-entered, it'll just return #N/A despite there being an obvious match. Post back your findings .. -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- |
#9
|
|||
|
|||
Formula with multiple variables
Sam,
It's kinda tough to nail down a moving target, you know .. Anyway, for your new twist on it, here's the irrefutable proof of how it looks like, & in working order: http://www.freefilehosting.net/download/3kc8d Multi Criteria Index Match.xls In Sheet1, I've array-entered* into B2: =INDEX('BO Data'!$C$2:$C$655, MATCH(1, (TRIM('BO Data'!$E$2:$E$655)=TRIM('SAP BW Data'!D2))* (TRIM('BO Data'!$F$2:$F$655)=TRIM('SAP BW Data'!B2))* (TRIM('BO Data'!$G$2:$G$655)=TRIM('SAP BW Data'!A2))* ('BO Data'!$D$2:$D$655=8),0)) The above is the working formula, all in the same cell, "decomposed" to make it simple to understand what's going on *To array-enter means to press CTRL+SHIFT+ENTER to confirm the formula. You gotta see Excel insert the curly braces: { } around the formula (look in the formula bar) as proof that its correctly done. P/s: I've watered down the range to just "$655" rows. Just use Edit Replace to change the $655 to $65533 (your big range - but I suggest you switch it to manual calc mode before doing this. Just press F9 to recalc.) -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- "Sam" wrote: Yes I verified I had the curlies. I even changed the data on the first row to make sure all the data was the same in both files. However, I have a new twist with the request that came up today. Same workbook and cell definition as previous posting: If Tab "DO Data" column E matches tab "SAP BW Data" column "D" and "DO Data" column F matches tab "SAP BW Data" column B and "DO Data" column G matches tab "SAP BW Data column A and "DO Data" column D equals 8 then return "DO Data" column C How will this change the formula you sent previously? Thanks. |
Thread Tools | |
Display Modes | |
|
|