Lookup table headers

[roddymc]

Hi

I have the following data set. I want to be able to build a formula that allows me to put in Group in Column A and a Rank in the table data) and the formula will return the Region in Row A.


Can someone please help?


Group Group/Type Name North East North West Yorkshire and the Humber East Midlands West Midlands East London South East South West Northern Ireland Scotland Wales
A City Prosperity 11 9 8 12 7 5 1 3 4 10 2 6
B Prestige Positions 11 5 9 10 4 2 12 1 6 8 3 7
C Country Living 11 10 9 5 7 4 12 6 2 1 8 3
D Rural Reality 9 11 7 6 10 5 12 8 4 1 3 2
E Senior Security 8 6 4 7 5 3 12 2 1 10 11 9
F Suburban Stability 4 1 6 2 5 7 12 10 8 3 11 9
G Domestic Success 11 8 9 6 10 2 4 1 7 3 5 12
H Aspiring Homemakers 9 7 6 1 5 3 12 2 4 10 11 8
I Family Basics 1 4 3 5 2 7 12 8 9 10 11 6

 

[roddymc]

Maybe this is clearer.... I would like to have a formula were I can input variables e.g A and 2 and the formula returns the header 'East'

Let me know if not clear. thank you

Group North East South
A 1 2 5
B 2 1 3
C 4 1 2

 

[Marcelo Branco]

Maybe this is clearer.... I would like to have a formula were I can input variables e.g A and 2 and the formula returns the header 'East'

Let me know if not clear. thank you

Group North East South
A 1 2 5
B 2 1 3
C 4 1 2

Try

=INDEX($A$1:$D$1,MATCH(G2,INDEX(A:D,MATCH(F2,A:A,0),0),0))

where F2 houses A (first criteria) and G2 houses 2 (second criteria)

Hope this helps

M.

 

[cbatrody]

Hi,

Try the following formula:

=INDEX($B$1:$D$1,1,MATCH($A9,INDEX(A$1:D$4,MATCH(A8,A$1:A$4,0),2):INDEX(A1:D4,MATCH(A8,A$1:A$4,0),4),0))

The data table is from A1:D4, and lookup values are in A8 & A9
A8=A, A9=2