Formula with multiple variables

[Sam]

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

 

[Pete_UK]

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

 

[Max]

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]

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]

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]

I think we are getting closer. I followed your instructions. Now it returns
#N/A.

Any more ideas?

 

[Max]

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

 

[Sam]

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]

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