sumproduct formula help
 

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?

sumproduct formula help
 

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:E350 00B1)*(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.


--
Biff
Microsoft Excel MVP


"ronda120" wrote in message
...
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?

sumproduct formula help
 

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.

"ronda120" wrote:

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?