Index Match with multiple criteria

[Emeng]

Hi all


I’m trying to Index/Match multiple criteria to find a variable date in a variable column.

The variables & lookup ranges are defined in named ranges.

My formula is

{=INDEX(AllDates,MATCH(1,(Vehicles=TARGET)*(AllDates=NxtSvc),1),DateCol)}

but throws #N/A, as do the simpler

{=INDEX(AllDates,MATCH(NxtSvc,DateCol,1)}

and

{=INDEX(DateCol,MATCH(NxtSvc,DateCol,1))}

In words it means find the Target vehicle in a row of Vehicles, (=DateCol)

look down that column & return the largest date less than or equal to today,

in the complete range of all dates for all vehicles.


Any help is much appreciated


Thanks & regards


Mark

 

[bosco_yip]

Try,

In T11,

=INDEX(AllDates,MATCH(NxtSvc,INDEX(AllDates,,DateCol),1),DateCol)

Regards

 

[Emeng]

Loving your work Bosco.
I had the "AllDates,,DateCol" happening separately in one of my previous attempts but the double commas look like an error. So much to learn!
Thanks & regards
Mark