Index Match - Skip Blanks Help

[Rob Mulhern]

I need some help wit an Index Match Formula.

Please see attached workbook.

The Formula for Columns C & D (I have just entered the dates for this example) are to bring back a date if the Task number = 2 or 4

Column C looks for a 2 and column D looks for a 4

Then in the "T Orders" Worksheet I use an Index Match to bring back the date from columns C & D

The issue I have is the index match in the "T Orders" worksheet brings back the first match which in this example is Blank as the first match is task 3 and therefore has no date

How can I get the Index Match to Skip blanks and keep matching until it finds the Date / Non Blank?

Or is there a better way?

Thanks.

 

[Debaser]

Maybe:
=LOOKUP(2,1/(TPlan[Works Order Ref.]=[@[Works Order]])/(TPlan[Fab Start Actual]<>""),TPlan[Fab Start Actual])
and
=LOOKUP(2,1/(TPlan[Works Order Ref.]=[@[Works Order]])/(TPlan[Fab End Actual]<>""),TPlan[Fab End Actual])

 

[bosco_yip]

Or……………….

In C2, copied right to D2 and all drag down :

=SUMIFS('T Plan'!C:C,'T Plan'!$B:$B,$A2)

Regards
Bosco

 

[Rob Mulhern]

Or……………….

In C2, copied right to D2 and all drag down :

=SUMIFS('T Plan'!C:C,'T Plan'!$B:$B,$A2)

Regards
Bosco

Works perfectly it seems.

Thanks very much