INDEX/MATCH SOLUTION EVADING ME

Copper Contributor

image001.png

 

I am attempting to return the value of other columns much like a VLOOKUP would; however, I need to find the applicable row/column to ensure a match and then if true return data from another column on that row and if there is no match I need to indicate as such. Attached is an example of what I have losing sleep over.

3 Replies

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,
,,,,,,,,
,,,,,,,,

@dakotaglory 

=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")

 

index match.png

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