Link to home
Start Free TrialLog in
Avatar of newparadigmz
newparadigmzFlag for United States of America

asked on

excel sumproduct or index match

please help me get the result "e" in the attached example, either by sumproduct or index match.

Thanks
spHelp.xlsx
Avatar of mvidas
mvidas
Flag of United States of America image
Hi NPZ,

Using INDEX and MATCH, with an array formula in the MATCH portion, can return what you need. Paste the following into H6, and press control-shift-enter. 
=INDEX($A$2:$A$8,MATCH(F6,IF($C$2:$C$8=G6,$B$2:$B$8),0))

Open in new window

Matt
Avatar of byundt
byundt
Flag of United States of America image
You could also use LOOKUP in a formula that does not need to be array-entered:
=LOOKUP(2,1/((B2:B8=F6)*(C2:C8=G6)),A2:A8)
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mvidas
mvidas
Flag of United States of America image
Brad,

Very impressive use of LOOKUP, though that comes as no surprise to me. Took me 10 minutes to wrap my head around your use of the reciprocal inverse of the vector. I had no idea it could do that. Kudos!

Matt
Avatar of byundt
byundt
Flag of United States of America image
Matt,
While you were away, we were regularly schooled by barryhoudini. That's one of his tricks, and pretty cute at that.

Brad
Avatar of newparadigmz
newparadigmz
Flag of United States of America image

ASKER

This was, in fact, exactly what I was looking for.

While the others worked, this one was 1, not an array formula, and 2, intuitive to understand at first glance.

Thanks All!