My data is here: My data is here

I am writing an array index match with 2 criteria, where I want to return the value in column 3 if my first data matches the Column 1 and is greater than or equal to value in Column 2.

For example, if my array is A and 67, then I want to have the formula return with the value in Column 3 that is equal to 9%.

I tried the following but I get a #VALUE! error:

=index(table1[column3], match(D1&E1, table1[column1]&table1[column2], -1))
  • I replicated your example just using ranges, and this formula entered as an array formula: =INDEX(A2:C13,MATCH(D1&E1,A2:A13&B2:B13,1),3). It worked. However, it took me a minute to realize that I had mistakenly entered 67 as the value instead of 67%. That gave me the wrong answer but not a #VALUE! error. But the formula above should give you a working starting point. – fixer1234 Sep 26 '17 at 20:22

It might be easier to just reference a regular index formula twice, with an if statement at the beginning.

=IF(YourArray% >= INDEXMATCHformulaReturningSecondColumnValue, INDEXMATCHformulaReturningThirdColumnValue, "Whatever you want to say if the two conditions aren't met")

I apologize that that isn't formatted in real Excel form, but hopefully you get the gist. I tested it in a self-made example and it works. The INDEXMATCHformula should return the Column 3 percentage and the if statement will provide your desired conditional statement.

  • Hi Jordan, Ctrl+Shift+Enter was pressed for the above, but #N/A was returned – excelmonkey99 Sep 28 '17 at 13:18
  • @excelmonkey99 Edited the answer with a different solution. Let me know if it helps! – Jordan Sep 28 '17 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.