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 above for reference. Highlighted the error in yellow hue(Sheet name - result).
m_tarler & HansVogelaar - Please help, Thanks in advance
You might use
=IFERROR(IFERROR(INDEX('RAW - reference '!$A$2:$A$72,MATCH(A2,'RAW - reference '!$B$2:$B$72,0)), INDEX('RAW - reference '!$A$2:$A$72,MATCH(A2,'RAW - reference '!$D$2:$D$72,0))),"")
If you have a recent version of Excel, you can also use
=XLOOKUP(A2,'RAW - reference '!$B$2:$B$72,'RAW - reference '!$A$2:$A$72,XLOOKUP(A2,'RAW - reference '!$D$2:$D$72,'RAW - reference '!$A$2:$A$72,""))
7 Replies
Perhaps you mean
=INDEX('RAW - reference '!$A:$A, IFNA( MATCH( A2,'RAW - reference '!$B:$B), MATCH( A2,'RAW - reference '!$D:$D,0)) )Thanks, but it is not desired outcome, even in the attached the result is not intact
HansVogelaar- I have added the link with edit access, whomever have the link only, but don't know why it is asking for login.
Kindly adding the file as a attachment. Please look into it and support.The link doesn't work - it requires a login.
HansVogelaar- I have verified the link is with general edit access with whomever having link.
Kindly attached the file, Please support for redressingYou might use
=IFERROR(IFERROR(INDEX('RAW - reference '!$A$2:$A$72,MATCH(A2,'RAW - reference '!$B$2:$B$72,0)), INDEX('RAW - reference '!$A$2:$A$72,MATCH(A2,'RAW - reference '!$D$2:$D$72,0))),"")
If you have a recent version of Excel, you can also use
=XLOOKUP(A2,'RAW - reference '!$B$2:$B$72,'RAW - reference '!$A$2:$A$72,XLOOKUP(A2,'RAW - reference '!$D$2:$D$72,'RAW - reference '!$A$2:$A$72,""))
