Indexing Woes

[Guest]

I use =INDEX(range,match(cell,range,),match(cell,range,)) formula to get
information from another worksheet/workbook daily. The problem comes from
data that is arranged like below, with multiple headings....How can I get it
to look up the information properly??


Provider1 Provider2
Age 2-3 years 4-7 years 8-12 years Overall 2-3 years 4-7 years 8-12
years Overall
Attribute1 74 43 16 36 74 43 16 36
Attribute2 2 2 2 2 2 2 2 2
Attribute3 4 8 14 10 4 8 14 10
Attribute4 2 6 11 7 2 6 11 7
Attribute5 3 6 5 5 3 6 5 5
Attribute6 5 7 6 6 5 7 6 6
Attribute7 1 3 5 4 1 3 5 4
Attribute8 6 18 32 22 6 18 32 22
Attribute9 3 7 8 7 3 7 8 7

 

[Bondi]

Hi Rianne,

If the above table starts in A1 (Provider 1 in B1 and Age in A2) then i
think something like this formula will work for picking out Provider 1,
Attribute 8 and 4-7 years:

=VLOOKUP("Attribute8",A3:I11,MATCH("Provider 1",B1:I1)+MATCH("4-7
years",B2:I2),FALSE)

Regards,
Bondi

 

[Guest]

One way:

I placed the names of "Providers" in column K rows 1 & 2 and headings of age
categories in Column L rows 1 to 4

K L
Provider1 2-3 years
Provider2 4-7 years
8-12 years
Overall


Assuming "providers" are first column of a set ie. B & F,then to find
column number use:

=(MATCH(K2,$B$2:$I$2,0)+MATCH(L3,B3:I3,0)) to find Provider 2, 4-7 years

Row:

=MATCH(A3,$A$1:$A$11,0) A3 = Attribute 1

A B C D E F G H I
Provider1 Provider2
Age 2-3 years 4-7 years 8-12 years Overall 2-3 years 4-7 years 8-12
years Overall
Attribute1 74 43 16 36 74 43 16 36
Attribute2 2 2 2 2 2 2 2 2
Attribute3 4 8 14 10 4 8 14 10
Attribute4 2 6 11 7 2 6 11 7
Attribute5 3 6 5 5 3 6 5 5
Attribute6 5 7 6 6 5 7 6 6
Attribute7 1 3 5 4 1 3 5 4
Attribute8 6 18 32 22 6 18 32 22
Attribute9 3 7 8 7 3 7 8 7

HTH

 

[Guest]

Just a further note: I am assuming INDEX function is based on A1. Adjust
row/column calculations as required for different INDEX base.

 

[Guest]

Thanks for the quick response...unfortunately I'm not permitted to alter the
format of the source of the data, I just pull it, and I'm trying to find a
work-around so I don't have to spend hours/days re-formatting data. We're
talking thousands of tabs.

Rianne

 

[Guest]

What was it about the solution, other than putting the "headings" in cells
(and this wasn't mandatory), that required the data to be reformatted? It was
using INDEX as you indicated in your posting (or as I understood it) so I am
a liitle confused!