Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Index Match Function Multiple Criteria 2 Columns 1 Row

Copper Contributor

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? 


3 Replies


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.

index match.png

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


I've just replied to the DM.