VLookup formula to extract data based on row and columns

[bremar]

Good day,

I’m not sure if vlookup is the right way to go, but this is all I really know. I am open to any suggestions to the following:

I have 2 sheets in my excel woorkwork:

Sheet one has the following data:
A2: Date
B2: Race
C2: LICNO
D2: NAME
E2: SURNAME
F2: SEX
G2: AGE
H2: CAT
I2: HMS

This sheet contains all club members who has participated in a particular race and their time so there are a number of repeats (example: Paul Smith has taken part in 10 races so far this year).

Now Sheet 2 is the one I want to create that consolidates all the info and it has the following columns
A2: Name
B2: Surname
C2: LICNO
D2: Race 1
E2: Race 2
F2: Race 3
G2: Race 4
H2: Race 5
I2: Race 6
Ect……

Column A, B and C have club members listed with no repeats:

A:Name B:Surname C:Licno D:Race 1 E: Race 2 F:Race 3 =>ect…
3 Paul Smith 3 ? ? ? ?
4 Mark Jupiter 987 ? ? ? ?
5 Peter Ample 410 ? ? ? ?
6 Robin Zulu 17 ? ? ? ?
7 June Thomas 89 ? ? ? ?
8 Rose Kimble 7 ? ? ? ?

So I want Paul Smith Time listed in columns D, E, F ect for the corresponding races. The Column (d onwards) headings are the exact words used in sheet 1 column B (rows)

I trust that this is enough info and that someone can help with the correct formula so that I can have the listed times for each row.

Regards

 

[steve the fish]

Hi. Have you ever used a pivot table? It seems your data in Sheet1 is set up so you could get what you wanted from them without the need for formulas.

 

[bremar]

Hi. Have you ever used a pivot table? It seems your data in Sheet1 is set up so you could get what you wanted from them without the need for formulas.

No I have not used pivot before

 

[steve the fish]

You will need column headers the highlight all your data and press insert tab then pivot table. If you get stuck come back.