Is there an alnernative to index match?

[Sordini]

OK,

Please see attached - I am looking for the simplest possible formula to do the job. (For training purposes)

I have the two yellow variants, and wish to return the green answer. The Part numbers can be in a random order and so can the columns.

Many thanks :-)

[canapone]

Hi, an alternative to cross-sum-if

=SUMPRODUCT(($A$2:$A$5=C9)*($B$1:$F$1=D9)*$B$2:$F$5)

=SUM(INDEX(($A$2:$A$5=C9)*($B$1:$F$1=D9)*$B$2:$F$5,))

Hope it helps


Edit. sorry, with my Excel I could see only yellows cells

[nawas]

You can use Vlookup function..

from your sample workbook, your lookup value in Cell A4,
then the formula =VLOOKUP(A4,A:E,5,0)

Thanks

[NBVC]

If the search values are in C10 (Part 21) and C11 (6) then this is another alternative:

=VLOOKUP(C10,$A$2:$F$5,MATCH(C11,$A$1:$F$1,0),0)

[Sordini]

Ooohhh
I like the VLOOKUP with MATCH....

Though SUMPRODUCT has its merits for being a little easier to train to people

Thank you so much for everything all!

[NBVC]

Note: Sumproduct on large tables can become less efficient...