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

sernum	FieldName	Value	Source	Date
1	con 1	4.0000	bon	16/02/2010
2	con 2	1.0000	bon	16/02/2010
3	con 3	.0000	cen	25/11/2010
4	con 4	.0000	cen	25/11/2010
5	con 5	.0000	cen	25/11/2010
6	con 6	70.7700	cen	13/01/2011
7	con 7	Yes	cen	21/03/2011
8	con 10	.0000	cen	13/01/2011
9	con 9	.0000	cen	30/06/2011
10	con 10	.0000	cen	30/06/2011
11	con 9	19/11/2008	bon	16/02/2010
12	con 12	21/10/1928	bon	29/03/2011
13	con 2	30/07/1999	bon	16/02/2010
14	con 3	.0000	bon	11/01/2011
15	con 4	.0000	bon	13/05/2010
16	con 5	1.0000	bon	11/01/2011
17	con 6	1.0000	bon	13/05/2010
18	con 18	.0000	bon	11/01/2011
19	con 19	.0000	bon	13/05/2010
20	con 20	M	cen	01/06/2011

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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
1	sernum	Reg	fieldname	value	source	date
2	1	1	con 1	red	bon	16/2/2010
3	2	1	con 2	2	bon	16/2/2010
4	3	1	con 3	0	bon	25/11/2010
5	4	1	con 4	0	cen	25/11/2010
6	5	1	con 5	70.7	cen	13/1/2011
7	6	2	con 2	4	bon	30/6/2011
8	7	2	con 6	0	egt	16/2/2011
9	8	2	con 1	orange	hk	13/5/2010
10	9	2	con 3	19/11/2009	bon	11/1/2010
11	10	2	con 4	30/5/1999	bon	12/6/2011
12	11	2	con5	0.00	get	12/6/2011
13	12	3	con 1	blue	bon	25/4/2011
14	13	3	con 2	5.5	bon	13/5/2011
15	14	3	con 3	27/8/2008	ken	30/6/2010
etc	....	....	....	....	....	....

<tbody>
</tbody>

 

[eagleaye]

format problem

 

[eagleaye]

format error

 

[eagleaye]

worksheet 2

	A	B	C	D	E	F	G	H	I	J	K	L
1	sernum	Reg	con 1	con 1 source	con 1 date	con 2	con 2 source	con 2 date	con 3	con 3 source	con 3 date	etc
2	1	1	red	bon	16/2/2010
3	2	1				2	bon	16/2/2010
7	6	2				4	bon	30/6/2011
9	8	2	orange	hk	13/5/2010
10	9	2							19/11/2009	bon	11/1/2010
13	12	3	blue	bon	25/4/2011
14	13	3				5.5	bon	13/5/2011
15	14	3							27/8/2008	ken	30/6/2010
etc	....	....	....	....	....	....

<tbody>
</tbody>

 

[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.