Forum Discussion

JMcAnarney's avatar
JMcAnarney
Copper Contributor
Jan 25, 2024

Index Match Function Multiple Criteria 2 Columns 1 Row

So I have the following Wage matrix that I need to look up two columns and 1 row ; columns being B and C and ROW being 25.  I am getting an error on this following screen shot:

 

 

Formula is as follows: =INDEX(D26:R45,MATCH(B48&B49,B26:B45&C26:C45,0),MATCH(B50,D25:R25,0))

 

I am getting a value error any guidance? 

 

excel

3 Replies

Sort By
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 25, 2024

    JMcAnarney 

    If you don't work with a recent version of Excel such as Office 365 or Excel for the web or Excel 2021 you have to enter the formula as an arrayformula with ctrl+shift+enter.

     

    Your formula works in my Excel 2013 sheet when it's entered with ctrl+shift+enter.

    • JMcAnarney's avatar
      JMcAnarney
      Copper Contributor
      Jan 25, 2024
      This works as search function but if on another sheet and dragging down formulas still is not working. I sent DM of brief file with formula ; can you look at and see if I am crazy

Resources