Forum Discussion
INDEX/MATCH SOLUTION EVADING ME
=IF(SUM(MMULT(TRANSPOSE(N(A2=$F$2:$H$6)),ROW($A$1:$A$5)^1)),SUM(MMULT(TRANSPOSE(N(A2=$F$2:$H$6)),ROW($A$1:$A$5)^1)),"not found")
This is the formula for row in cell B2. The formula is filled down and returns an error message if the value isn't found. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
In cell C2:
=IF(SUM(MMULT(N(A2=$F$2:$H$6),ROW($A$1:$A$3)^1)),SUM(MMULT(N(A2=$F$2:$H$6),ROW($A$1:$A$3)^1)),"not found")
The formula is filled down and returns an error message if the value isn't found. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
In cell D2:
=IFERROR(INDEX($F$2:$H$6,B2,C2),"not found")
In cell E2:
=IFERROR(INDEX($I$2:$I$6,B2),"not found")
OliverScheurich OMG! Thank you so very much. I would never have figured that out. I need to learn about MMULT function and practice. That worked perfectly. I have spent hours and hours and broke down match and index to simplify it to the point of confusion so I am extremely grateful. Thank you.