Forum Discussion

Texas1870's avatar
Texas1870
Copper Contributor
Jul 15, 2024

Index Match only blank cells

I have a dataset which contains only partial data in one column (SAP below). I have the data in another table, but I only want to INDEX/MATCH the blank fields in the SAP column below. Any thoughts how to accomplish this?

 

LocationCodeSAP
Texas101 
Texas101

TX

New Mexico102 
New Mexico102NM
excel
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jul 15, 2024

    Texas1870 

    =IFERROR(INDEX(A$2:A$5,SMALL(IF($C$2:$C$5="",ROW($C$2:$C$5)-1),ROW($A1))),"")

     

    This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.

    • Texas1870's avatar
      Texas1870
      Copper Contributor
      Jul 15, 2024
      Thanks OliverScheurich. Let me clarify because I missed a detail. I need to fill in only the blank fields in the SAP column. I'm guessing this might need a "helper" column and then copy/paste into the original.
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Jul 15, 2024

        Texas1870 

        Your are welcome. I misunderstood your question unfortunately and i'm still not 100 % sure if i understand correctly. Please check if the attached sample file works for you. All the data for the SAP column is now in column D. This could be the "helper" column. Then you can copy the whole column D and paste only values in column C.

Resources