Forum Discussion
IF AND OR THEN ???
=($B$11*(100%+INDEX($B$3:$E$5,MATCH($B$12,$A$3:$A$5,0),MATCH($B$11,$B$2:$E$2,1))))Does this return your expected result?
One of the "fun things" about Excel is that there are always multiple ways to get from point A to point B.
OliverScheurich has shown a way that dealt with the whole array of numbers and did so with a set functions that have been around for many years (INDEX and MATCH)
I've incorporated those same functions in a LET formula that might make for easier reading, but LET requires the 2021 edition or newer of Excel.
=LET(
tcol,MATCH(B11,B2:E2,1),
trow,MATCH(B12,A3:A5,0),
B11*(1+(INDEX(B3:E5,trow,tcol)))
)
The LET function enables you to define values, and then use them in the final calculation. In this case,
tcol is defined as the number of the column with cells B2:E2 that most appropriately matches the value in cell B11
trow is the number of the row that matches the quality word (good, avg, bad)
and then the formula multiplies B11 by 1 + the value in the "tcol-th" column and "trow-th" row of the range B3:E5