match row value and column name to return different column value

[eagleaye]

I'm trying to reformat some data and looking for a formula that can match a value in a row, look up a value in the row, then if the cell value matches the column heading return values from a further two columns.
Here's some sample data that might make my aims clearer, I want to match the cell value under fieldName in worksheet 1 with the column name in worksheet 2 and fill the other related columns with the Source and Date values from worksheet 1.

Worksheet 1


[TABLE="width: 261"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]sernum[/TD]
[TD]FieldName[/TD]
[TD]Value[/TD]
[TD]Source[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]con 1[/TD]
[TD]4.0000[/TD]
[TD]bon[/TD]
[TD]16/02/2010[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]con 2[/TD]
[TD]1.0000[/TD]
[TD]bon[/TD]
[TD]16/02/2010[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]con 3[/TD]
[TD].0000[/TD]
[TD]cen[/TD]
[TD]25/11/2010[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]con 4[/TD]
[TD].0000[/TD]
[TD]cen[/TD]
[TD]25/11/2010[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]con 5[/TD]
[TD].0000[/TD]
[TD]cen[/TD]
[TD]25/11/2010[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]con 6[/TD]
[TD]70.7700[/TD]
[TD]cen[/TD]
[TD]13/01/2011[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]con 7[/TD]
[TD]Yes[/TD]
[TD]cen[/TD]
[TD]21/03/2011[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]con 10[/TD]
[TD].0000[/TD]
[TD]cen[/TD]
[TD]13/01/2011[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]con 9[/TD]
[TD].0000[/TD]
[TD]cen[/TD]
[TD]30/06/2011[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]con 10[/TD]
[TD].0000[/TD]
[TD]cen[/TD]
[TD]30/06/2011[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]con 9[/TD]
[TD]19/11/2008[/TD]
[TD]bon[/TD]
[TD]16/02/2010[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]con 12[/TD]
[TD]21/10/1928[/TD]
[TD]bon[/TD]
[TD]29/03/2011[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]con 2[/TD]
[TD]30/07/1999[/TD]
[TD]bon[/TD]
[TD]16/02/2010[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]con 3[/TD]
[TD].0000[/TD]
[TD]bon[/TD]
[TD]11/01/2011[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]con 4[/TD]
[TD].0000[/TD]
[TD]bon[/TD]
[TD]13/05/2010[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]con 5[/TD]
[TD]1.0000[/TD]
[TD]bon[/TD]
[TD]11/01/2011[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]con 6[/TD]
[TD]1.0000[/TD]
[TD]bon[/TD]
[TD]13/05/2010[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]con 18[/TD]
[TD].0000[/TD]
[TD]bon[/TD]
[TD]11/01/2011[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]con 19[/TD]
[TD].0000[/TD]
[TD]bon[/TD]
[TD]13/05/2010[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]con 20[/TD]
[TD]M[/TD]
[TD]cen[/TD]
[TD]01/06/2011[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2

sernum con 1 con 1 source con 1 date con 2 con 2 source con2 date con 3 con 3 source con 3 date1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Hope this makes sense, any help much appreciated

 

[teylyn]

Hi, It's not quite clear what you would like to achieve. Please mock up the result manually, preferably in a real spreadsheet and post (use a file sharing site) that for inspection.

 

[eagleaye]

Thanks teylyn, I suspected I was being a little unclear hopefully the sheets below might make more sense.

Essentially I want to match the "sernum" value from worksheet 2 with that in worksheet 1, then look up the worksheet 1 column D value, if it matches the column header in worksheet 2 populate corresponding cells with values from column C,E,F and G in worksheet 2

worksheet 1

A	B	C	D	E	F	G
sernum	Reg	fieldname	value	source	date
1	1	con 1	red	bon	16/2/2010
2	1	con 2	2	bon	16/2/2010
3	1	con 3	0	bon	25/11/2010
4	1	con 4	0	cen	25/11/2010
5	1	con 5	70.7	cen	13/1/2011
6	2	con 2	4	bon	30/6/2011
7	2	con 6	0	egt	16/2/2011
8	2	con 1	orange	hk	13/5/2010
9	2	con 3	19/11/2009	bon	11/1/2010
10	2	con 4	30/5/1999	bon	12/6/2011
11	2	con5	0.00	get	12/6/2011
12	3	con 1	blue	bon	25/4/2011
13	3	con 2	5.5	bon	13/5/2011
14	3	con 3	27/8/2008	ken	30/6/2010
....	....	....	....	....	....

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]

[TD="bgcolor: #CACACA, align: center"]etc[/TD]

</tbody>

 

[eagleaye]

format problem

 

[eagleaye]

format error

 

[eagleaye]

worksheet 2

[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #CACACA"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD]sernum[/TD]
[TD]Reg[/TD]
[TD]con 1[/TD]
[TD]con 1 source[/TD]
[TD]con 1 date[/TD]
[TD]con 2[/TD]
[TD]con 2 source[/TD]
[TD]con 2 date[/TD]
[TD]con 3[/TD]
[TD]con 3 source[/TD]
[TD]con 3 date[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]red[/TD]
[TD]bon[/TD]
[TD]16/2/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]bon[/TD]
[TD]16/2/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]bon[/TD]
[TD]30/6/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]orange[/TD]
[TD]hk[/TD]
[TD]13/5/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]19/11/2009[/TD]
[TD]bon[/TD]
[TD]11/1/2010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]13[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]blue[/TD]
[TD]bon[/TD]
[TD]25/4/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]14[/TD]
[TD]13[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5.5[/TD]
[TD]bon[/TD]
[TD]13/5/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]15[/TD]
[TD]14[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]27/8/2008[/TD]
[TD]ken[/TD]
[TD]30/6/2010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]etc[/TD]
[TD]....[/TD]
[TD]....[/TD]
[TD]....[/TD]
[TD]....[/TD]
[TD]....[/TD]
[TD]....[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[eagleaye]

Is anyone able to help with this?
I've got so far trying combinations of vloopup, hlookup and index match but end up populating the column rows in worksheet 2 with the same values as I can't work out how to match the sernum columns.