Sum product

[Guest]

I'm using the following sumproduct formula to look up two pieces on
information on a spreadsheet and then return the text that is in column A but
keep getting a #value! error. All the formatting is set to general so I'm
not sure what I am doing wrong. Any advice?

=sumproduct(('Tab1'!B2:B1199=A3)*('Tab1'!C2:C1199=B3)*('Tab1'!A2:A1199))

 

[Guest]

The problem is sumproduct sums numbers it does not return text.

 

[Domenic]

Try the following formula instead...

=INDEX(Tab1!A2:A1199,MATCH(1,(Tab1!B2:B1199=A3)*(Tab1!C2:C1199=B3),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Guest]

Is there another function I can use to look up two pieces of information and
then return text that is in another column?

 

[Bob Phillips]

=INDEX('Tab1'!A2:A1199,MATCH(1,('Tab1'!B2:B1199=A3)*('Tab1'!C2:C1199=B3),0)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thank you. I'm still getting a NAME? error. Any thoughts on that one?

 

[Guest]

Never mind. I figured out what I was doing wrong. Thanks so much Domenic
for the formula, it works great!!!!