Forum Discussion

dakotaglory's avatar
dakotaglory
Copper Contributor
Jan 29, 2024

INDEX/MATCH SOLUTION EVADING ME

 

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.

  • dakotaglory's avatar
    dakotaglory
    Copper Contributor

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

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor

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

       

      • dakotaglory's avatar
        dakotaglory
        Copper Contributor

        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.

Resources