Index Match only blank cells

Question

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?

Location	Code	SAP
Texas	101
Texas	101	TX
New Mexico	102
New Mexico	102	NM

oliverscheurich · Answer

Texas1870&nbsp;=IFERROR(INDEX(A$2:A$5,SMALL(IF($C$2:$C$5="",ROW($C$2:$C$5)-1),ROW($A1))),"")&nbsp;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 · Answer

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 · Answer

Texas1870&nbsp;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.

peterbartholomew1 · Answer

Texas1870&nbsp;You are correct that you cannot 'fill in the blanks' using a formula.&nbsp; After all, were you to succeed, they would no longer be blank so you wouldn't fill them in.&nbsp; What you could do instead, is simply read the original data, leaving it unaltered.&nbsp; A table with the blanks filled in could then be created somewhere else using a formula of the form&nbsp;= HSTACK(
    Location,
    Code,
    IF(ISBLANK(SAP), 
      XLOOKUP(Location, State, Abbreviation), 
      SAP)
  )&nbsp;&nbsp;

Forum Discussion

Index Match only blank cells

Resources