Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indexing Woes
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indexing Woes
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indexing Woes
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 "Rianne" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indexing Woes
Just a further note: I am assuming INDEX function is based on A1. Adjust
row/column calculations as required for different INDEX base. "Bondi" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indexing Woes
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 "Toppers" wrote: 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 "Rianne" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indexing Woes
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! "Rianne" wrote: 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 "Toppers" wrote: 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 "Rianne" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Indexing Function | Excel Worksheet Functions | |||
Indexing value fro each new worksheet | Excel Worksheet Functions | |||
Is Indexing the right option? | Excel Worksheet Functions | |||
excel formula/function woes | Excel Worksheet Functions | |||
Pivot table woes. | Excel Worksheet Functions |