How to perform lookup or index on filtered blank cells

I’m trying to achieve the following automation in one of the Excel worksheets to populate some blank cells (screenshot below):

  1. Filter Excel worksheet for blank cells in column I (highlighted in red)

  2. Write a vlookup or index match formula to the first filtered row in column I (in this case it is cell I34474). If the formula was entered manually it would look like this:
    =INDEX(I:I,MATCH(K34474,K:K,0))

  3. Copy the formula down to rest of filtered blank cells in column I

The issue I’m having is not knowing how to write the vlookup or index match formula to pick up that first filtered row number dynamically/automatically. The result of the filtering will change each time with a new file so it needs to be dynamic to cater for the changing row number.

Does anyone have any suggestions on how to best tackle this? Thanks in advance!

@kerri.tiam2,

I would suggest you to use macro/vba for this, we can easily get first row number after filter an excel.
There are plenty of articles on google.

you can visit a weblink as below,