Sumproduct - Return a String

[carlyman]

When using SUMPRODUCT, is it possible to have the function return a
String instead of only numbers?

Example:
A1:Z1 - Range (1-2) Repeating
A2:Z2 - Alphabet (A-Z)
A3:Z3 - Cell ID (A3, B3, etc)

E1: =SUMPRODUCT(--(1=A1:Z1)*--("A"=A2:Z2)*A3:Z3)

If I do my array math correctly, it should be: {1,0,1,0....,1,0} *
{1,0,0,0,....,0} * {"A3","B3",...."Z3"} = "A3" -- but instead, I
recieve the #VALUE! error.

Any ideas?

Thanks,
JC

 

[KL]

Hi carlyman,

If I do my array math correctly, it should be: {1,0,1,0....,1,0} *
{1,0,0,0,....,0} * {"A3","B3",...."Z3"} = "A3" -- but instead, I
recieve the #VALUE! error.

I am afraid you don't do your math correctly Have you ever seen a text
string, say "A3", to be multiplied by 0 or 1 and return anything, but
errorr?

If I have understood correctly, you could do something like this:

=INDEX(A3:Z3,MATCH("2E",A1:Z1&A2:Z2,0))

This formula has to be array-entered (Ctrl+Shift+Enter)

Regards,
KL

 

[Govind]

Hi,

Try using

=OFFSET(A1,SUMPRODUCT(--(A1:Z1=1),--(A2:Z2="A"))+1,0)

to return the cell ID.

I presume you have only one combination of 1 in row 1 and "A" in row 2.

Regards

Govind.

 

[olasa]

Yes, try this:
=INDEX(A3:Z3,1,MATCH(1,(1=A1:Z1)*("A"=A2:Z2),0))

HTH
Ola Sandström

 

[KL]

Hi Govind,

I don't think your formula can possibly work, as
SUMPRODUCT(--(A1:Z1=1),--(A2:Z2="A"))+1 would always return either 1 or 2
(assuming that the combinations like "1A" are unique in the table), so no
matter what combunation you search you'll get the value of the cell [A2] or
[A3].

Am I missing something?

Regards,
KL

 

[Govind]

Hi KL,

You are right. I stand corrected. Sorry about that.

Govind.

Hi Govind,

I don't think your formula can possibly work, as
SUMPRODUCT(--(A1:Z1=1),--(A2:Z2="A"))+1 would always return either 1 or 2
(assuming that the combinations like "1A" are unique in the table), so no
matter what combunation you search you'll get the value of the cell [A2] or
[A3].

Am I missing something?

Regards,
KL

Hi,

Try using

=OFFSET(A1,SUMPRODUCT(--(A1:Z1=1),--(A2:Z2="A"))+1,0)

to return the cell ID.

I presume you have only one combination of 1 in row 1 and "A" in row 2.

Regards

Govind.