Lookup function/macro help

[Hieronymous]

Hi,

I am trying to carry out an operation where I have a Timeline worksheet (as shown below) and want to be able to carry out a vlookup operation on a Summary worksheet (also below).

What I want to happen is that each cell in the Timeline worksheet in the range D2:D31 up to FR2:FR31to look at the Summary sheet, and if the Product ID matches (column B in both sheets) then it does a vlookup on the date (Row 1 in Timeline, and Column A in Summary) and brings over the value if there is a match.

I would like to have any #N/A results display as blank instead, and was going to use a nested IF(ISNA) formula for that, but I can't work out how to make the VLookup conditional on the project ID matching.

Thanks in advance!

Timeline sheet:

Summary sheet:

 

[ParamRay]

You could create a custom VBA function, such as:

Function TASK_DETAILS(date_value, project_value)

  Dim r As Long
  Dim c As Long
  Dim table_array
  Dim j As Long
  
  With ThisWorkbook.Worksheets("Summary")
    r = .Cells(.Rows.Count, 1).End(xlUp).Row
    c = .Cells(1, .Columns.Count).End(xlToLeft).Column
    table_array = Range(.Cells(2, 1), .Cells(r, c)).Value
  End With
  
  TASK_DETAILS = vbNullString
  For j = LBound(table_array, 1) To UBound(table_array, 1)
    If table_array(j, 1) = date_value And table_array(j, 2) = project_value Then
      TASK_DETAILS = table_array(j, 3)
      Exit For
    End If
  Next j

End Function

Place the above function in a standard code module within your workbook.

You can then enter the following formula into cell D2 on your "Timeline" worksheet, and then drag the formula down and to the right:

=TASK_DETAILS(D$1,$B2)