Index and Match function across multiple rows with repeating names

Try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. after placing the formula in the cell, hold down the Ctrl+Shift and then press Enter)

Please Login or Register  to view this content.

and then drag down until you get blank cells.

See the attached sheet.

If you are adding more columns, you need to match the range reference (in red) correctly according to your data for this formula to work correctly...

=IFERROR(INDEX($B$2:$M$45,MATCH($O$2,$A$2:$A$45,0),SMALL(IF($B$1:$M$1=$O4,COLUMN($B$1:$M$1)-COLUMN($B$1)+1),COLUMNS($P4:P4))),"")

where
B2:M45 is the total data range.
A2:A45 is the range containing Day nos.
O2 is the cell where you have input the day lookup value.
B1:M1 is the range containing vehicle types. you need to change it accordingly. Like if your last column is say AA1, the correct range will be B1:AA1
O4 is the cell where the formula has lookup value for the vehicle type.
In COLUMN($B$1:$M$1)-COLUMN($B$1), say if your last column is AA1, this will become COLUMN($B$1:$AA$1)-COLUMN($B$1)
In COLUMNS($P4:P4), P4 is the first cell where you put this formula, So if you have more columns and you want to place this formula in say AD4, this part will become COLUMNS($AD4:AD4).

Change all the ranges according to your actual data, and this formula will work.

Hope this helps.

See the attached sheet.

If you are interested:

I would make this using VBA to get the data in an new sheet that can be used in a pivot table.

After that I make an pivot table.

Please reply if you are interested in that solution.

You're welcome. Thanks for the feedback.
If that takes care of your original question, please mark your thread as solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.