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:

 

JMcAnarney_0-1706195142293.png

 

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

@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.

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

@JMcAnarney 

I've just replied to the DM.