Lookup and Transpose

[LJREdinburgh]

I have a large dataset of annual information for three cities and an average broken down by month with according data for various categories generated vertically. For chart creation and ease of reading, I want to show this horizontally and ordered differently. Below is a short example of what I need.

Area	Occ
EDI01-09	0.54
GLA01-09	0.51
ABD01-09	0.52
SCO01-09	0.52
EDI02-09	0.81
GLA02-09	0.66
ABD02-09	0.7
SCO02-09	.72

<tbody>
</tbody>

What I want is:

	Jan-09	Feb-09
EDI	0.54	0.81
GLA	0.51	0.66
ABD	0.52	0.7
SCO	0.52	0.72

<tbody>
</tbody>

This data set goes from 01/01/2009 up until 31/07/2014. So ideally I would have a lookup-formula that I can drag across to populate the information without having to copy and paste and/or transpose the information. I imagine there would be some set-up involved, i.e renaming certain headings to get this to work initially?

 

[Chris Mack]

Assuming your data is in 'Sheet1', and the results are on a separate sheet, put this formula in B2 and then copy it across and down:

=VLOOKUP($A2&TEXT(B$1,"mm")&"-"&TEXT(B$1,"yy"),Sheet1!$A:$B,2,FALSE)

Hope this helps,

Chris.

 

[LJREdinburgh]

Thanks Chris! It worked beautifully!

 

[Chris Mack]

You're welcome.