Forum Discussion
INDEX/MATCH SOLUTION EVADING ME
CSV
Lookup Reference,WHAT ROW IS LOOKUP IN,WHAT COLUMN,INDEX/MATCH RESULT,"VALUE OF DATA IN ""DATA I NEED"" WHERE A# MATCHES F2:H6",COL1,COL2,COL3,DATA I NEED
pear,,,,,random,random,apples,myvalue1
apples,,,,,random,oranges,random,myvalue2
oranges,,,,,strawberries,random,random,myvalue3
strawberries,,,,,random,random,banana,myvalue4
banana,,,,,random,no-match,random,
,,,,,,,,
,,,,,,,,
=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.