Nested IFs into 2 possible INDEX/MATCH functions

0
votes

I am trying to use a simple nested IF statement to run one of two INDEX/MATCH functions.

The logic is, if column C contains the word "yes" then I want INDEX/MATCH a certain array for two criteria, and return the corresponding result in column 13. If column C contains the word "no" then I want INDEX/MATCH the identical array for the two same criteria, BUT return the corresponding result in column 14. In both cases, the result is then multiplied by an integer in column D.

The formula I have is below. Note the array is on a separate sheet. Right now this formula works correctly when the column C value is "No." But when it is set to "Yes" then I get a #REF error.

=IF(C4="No",(INDEX('2015 RVU data (do not edit)'!$A$8:$M$8922,MATCH(1,('RVU input'!A4='2015 RVU data (do not edit)'!$A$8:$A$8922)*('RVU input'!B4='2015 RVU data (do not edit)'!$B$8:$B$8922),0),13)D4),IF(C4="Yes",(INDEX('2015 RVU data (do not edit)'!$A$8:$M$8922,MATCH(1,('RVU input'!A4='2015 RVU data (do not edit)'!$A$8:$A$8922)('RVU input'!B4='2015 RVU data (do not edit)'!$B$8:$B$8922),0),14)*D4))

1
excelif-statementindexingexcel-formulamatch
Rather than repeating the long INDEX function you could have just one instance of that with a simple IF function in place of the column index, e.g =IF(C4="No",13,14) – barry houdini

1 Answers

0
votes

Sorry guys I figured this out.

The formula is correct, BUT I forgot to expand the array size specified in the INDEX statement to include the 14th column that is referenced in the 2nd IF statement.