Search code examples
excelformula

Fill this missing name from Column C Excel Formula

I have list of data where Column A has Duplicate ID's. In the Column B where you see the Text Searched which Name is empty that needs to be fill.

I am looking for a formula which takes the Name from Column C where there is last Viewed Page value in Column B for similar ID in Column A which Name is empty.

Take the Nameand paste in Column D. Your help will be greatly appreciated.

Download copy

enter image description here

Solution

  • If you have Office 365, try:

    D2: =IF(C3<>"","",LET(
      x, FILTER($C$2:C3,($A$2:A3=A3)*($B$2:B3="Viewed Page")),
      y, COUNTA(x),INDEX(x,y)))

    and fill down

    If you have an older version, try:

    D2: =IF(C2<>"","",INDEX($C$1:C1,AGGREGATE(14,6,1/(($A$2:A2=A2)*($B$2:B2="Viewed Page"))*ROW($B$2:B2),1)))

    Note the array argument for the INDEX function starts at C1 and not C2. This is because the AGGREGATE function is returning the absolute row number, and not the index into the array.