MATCH/INDEX to reference three cells against each other?

On the attached, on "Vehicle summary" tab, in cell B4 (i.e. cross section of 01/10/11 with EX61XUU) I want the result to be "Derek Anderson".

I want excel to draw this info from tab "Driver summary" cell B2. (Columns

So for 06/10/11, EX61XUU was being used by "Ian Archibold". "Ian Archibold" should be the result in cell G9 on tab "Vehicle summary".

I've tried colour coded the info that i was referencing with each other.

Please can you let me know if you can help.

Thanks,

Have a look at the attached.

Assuming you don't only have the 2 drivers, then list the names of the drivers in the sheet somewhere. I chose to list them in R4:R5. Then name the list, by selecting it and typing a name in the Name box to the left of the formula bar at top. I chose "Names".

Then formula to extract the name corresponding to the date and Vehicle would be in B4:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and not just ENTER and copied down.

the corresponding formula in G4 for the other vehicle is:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and not just ENTER and copied down.


Formula to get other data, like Load ref(s) is, in C4:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and not just ENTER and copied down.

to get other data, you need to replace the 8 near the end with the column number of interest. I wasn't exactly sure where Time should come from....

To work with other vehicle, change the reference to $B$2 to $G$2.....

This all assumes the same vehicle is not used by multiple drivers on the same day. If so, only the first driver's info will come into the sheet.