sumproduct formula help

[ronda120]

Trying to match 3 fields in one dataset and return a text value in a 4th field.

=SUMPRODUCT(--('[Copy of
Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$A2:$A35000=--LEFT(E4,5)),--('[Copy
of
Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$E2:$E35000=--MID(E4,6,10)),--('[Copy
of
Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$F2:$F35000=--RIGHT(E4,10)),('[Copy
of Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$AD2:$AD35000))


The value I am trying to return in column AD is text. Should I alter the
formula because the return is a 0?

 

[T. Valko]

SUMPRODUCT won't return TEXT values.

I would use cells to hold:

A1: =--LEFT(E4,5)
B1: =--MID(E4,6,10)
C1: =--RIGHT(E4,10)

Then this array formula** (I'm leaving out the path/sheet names so add it to
your formula):

=INDEX(AD2:AD35000,MATCH(1,(A2:A35000=A1)*(E2:E35000B1)*(F2:F35000=C1),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.

 

[Sheeloo]

SUMPRODUCT is for finding the SUM of PRODUCT of two or more arrays...

Use Vlookup or Index/Match to lookup the value.

See http://www.contextures.com/xlFunctions03.html for ideas.