I have the following chart:

Is it possible to use XLOOKUP function to filter the data by "Car" or "Bike", Year and the Index (column B)?

I can filter by year and index, but Im not sure if its possible to add the "Car" or "Bike" filter condition.

CodePudding user response：

I would use INDEX/MATCH/MATCH for this:

=INDEX($C$4:$F$6,MATCH(2,$B$4:$B$6,0),MATCH("Car",$C$2:$F$2,0) MATCH(2022,$C3:$D$3)-1)

This works when the year division are the same order and number as the other major division. It first finds the relative column of the larger division then adds the offset for the year and subtracts 1. This will return the correct value, in this case e