Forum Discussion
Index & Match formula not working on multiple column
Hi Folks,
I'm facing error on the match formula, which I tried to apply, I'm getting #N/A error... I tried few trouble shoots to make it work (adding helper column, broke the formula with "&" to make it work, checking for any additional characters & checking the format etc) but it is not working.
I have used Match formula earlier, but never had this issue. I want a single formula to MATCH & verify multiple column to return the desired output, I can do single column verification & again to do the next column verification and get the result but it is tedious and not time saving. Kindly help me to learn how to make a formula work to one validation of multiple column.
Kindly attaching the sheet for reference
=IFNA(INDEX('RAW - reference '!$A$2:$A$72,MATCH(TRUE,(('RAW - reference '!$B$2:$B$72=Result!A2)+('RAW - reference '!$D$2:$D$72=Result!A2))>0,0)),"")You are welcome. Please share your expected result with your sample file. Does this formula return the intended output?
7 Replies
=INDEX($C$2:$C$15,MATCH(1,($A$2:$A$15=E3)*($B$2:$B$15=F3),0))This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
Here's an advanced example for INDEX and MATCH with multiple columns and approximate match:
INDEX and MATCH approximate match with multiple criteria - Excel formula | Exceljet
OliverScheurich - Thanks for the update... but I use office 365... I'm not using the formula on web excel.
Kindly attaching the sheet, please apply the formula in the data sheet highlighted with yellow hue
INDEX and MATCH works in Office 365 and Excel for the web and in legacy Excel versions such as Excel 2013. Thanks for sharing a sample file and please don't publish content with sensitive data such as names of real people. Do the formulas in the attached sample file return the intended result?
