Excel - Formula with multiple variables

Asked By Sa on 30-Jul-08 06:29 PM
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 replied on 30-Jul-08 10:03 PM
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.


ng the
demechani replied on 30-Jul-08 06:54 PM
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)
--(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
Downloads:16,400 Files:356 Subscribers:53
Sa replied on 30-Jul-08 07:55 PM
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?
demechani replied on 30-Jul-08 09:12 PM
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

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

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

And if the above works for you (it should), press the "Yes" button below
from where you're reading this
Downloads:16,400 Files:356 Subscribers:53
Sa replied on 30-Jul-08 10:12 PM
I think we are getting closer.  I followed your instructions.  Now it returns

Any more ideas?
demechani replied on 31-Jul-08 12:02 AM
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

Post back your findings ..
Downloads:16,400 Files:356 Subscribers:53
Sa replied on 01-Aug-08 03:56 AM
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
then return "DO Data" column C

How will this change the formula you sent previously?

demechani replied on 01-Aug-08 04:58 AM

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:
Multi Criteria Index Match.xls

In Sheet1,

I've array-entered* into B2:

=INDEX('BO Data'!$C$2:$C$655,
(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,

*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.)
Downloads:16,400 Files:356 Subscribers:53