Discussion Groups Home

Return a value from a table using Column and row names as input? in Excel General Questions

11/23/2006 4:34 AM PST

Question

	What cellformula, using column and row names, return a specific value from a table like below? (I cant use the INDEX function since I dont know the column or row numbers in the actual table, only the names) A B C D F 1 Week 42 Week 43 Week 44 Week 45 2 Company 1 67% 71% 69% 81% 3 Company 2 45% 78% 79% 73% 4 Company 3 88% 67% 86% 74% 5 Company 4 97% 56% 77% 92% 6 Company 5 87% 55% 82% 69%

Was this post helpful to you?

Top

Roger Govier

11/23/2006 4:46 AM PST

Answer

Hi

Use the MATCH function as well as INDEX.

=INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1:$1,0))

Substitute the word Name and Week with the cell references holding the
Company and Week Number or insert their text as "Company2" and "Week 44"
for example.

--
Regards

Roger Govier

"Olle" <Olle@discussions.microsoft.com> wrote in message
news:C260FDD6-032D-4DFF-B12B-45814081668C@microsoft.com...
> What cellformula, using column and row names, return a specific value
> from a
> table like below? (I cant use the INDEX function since I dont know the
> column
> or row numbers in the actual table, only the names)
>
> A B C D F
>
> 1 Week 42 Week 43 Week 44 Week 45
> 2 Company 1 67% 71% 69% 81%
> 3 Company 2 45% 78% 79% 73%
> 4 Company 3 88% 67% 86% 74%
> 5 Company 4 97% 56% 77% 92%
> 6 Company 5 87% 55% 82% 69%
>
>
>
>
>

Was this post helpful to you?

Top

Olle

11/23/2006 5:43 AM PST

Thank you very much for your quick response!

I tried your suggested formula and with some minor adjustments it almost got
it to work. Using the below formula I don't get the "Company 5, Week 45"
value, I get the "Company 4, Week 44" instead. How can that be? Solution?

=INDEX($A$1:E$6;MATCH("Company 5";A2:A6;0);MATCH("Week 45";B1:E1;0))

Regars

Olle

"Roger Govier" wrote:

> Hi
>
> Use the MATCH function as well as INDEX.
>
> =INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1:$1,0))
>
> Substitute the word Name and Week with the cell references holding the
> Company and Week Number or insert their text as "Company2" and "Week 44"
> for example.
>
> --
> Regards
>
> Roger Govier
>
>
> "Olle" <Olle@discussions.microsoft.com> wrote in message
> news:C260FDD6-032D-4DFF-B12B-45814081668C@microsoft.com...
> > What cellformula, using column and row names, return a specific value
> > from a
> > table like below? (I cant use the INDEX function since I dont know the
> > column
> > or row numbers in the actual table, only the names)
> >
> > A B C D F
> >
> > 1 Week 42 Week 43 Week 44 Week 45
> > 2 Company 1 67% 71% 69% 81%
> > 3 Company 2 45% 78% 79% 73%
> > 4 Company 3 88% 67% 86% 74%
> > 5 Company 4 97% 56% 77% 92%
> > 6 Company 5 87% 55% 82% 69%
> >
> >
> >
> >
> >
>
>
>

Was this post helpful to you?

Top

Olle

11/23/2006 5:57 AM PST

Dont mind my last question, Solved it, thx. Right formula becomes:

=INDEX($A$1:E$6;MATCH("Company 2";A1:A6;0);MATCH("Week 43";A1:E1;0))

Regards

Olle

"Olle" wrote:

> Thank you very much for your quick response!
>
> I tried your suggested formula and with some minor adjustments it almost got
> it to work. Using the below formula I don't get the "Company 5, Week 45"
> value, I get the "Company 4, Week 44" instead. How can that be? Solution?
>
> =INDEX($A$1:E$6;MATCH("Company 5";A2:A6;0);MATCH("Week 45";B1:E1;0))
>
> Regars
>
> Olle
>
>
>
> "Roger Govier" wrote:
>
> > Hi
> >
> > Use the MATCH function as well as INDEX.
> >
> > =INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1:$1,0))
> >
> > Substitute the word Name and Week with the cell references holding the
> > Company and Week Number or insert their text as "Company2" and "Week 44"
> > for example.
> >
> > --
> > Regards
> >
> > Roger Govier
> >
> >
> > "Olle" <Olle@discussions.microsoft.com> wrote in message
> > news:C260FDD6-032D-4DFF-B12B-45814081668C@microsoft.com...
> > > What cellformula, using column and row names, return a specific value
> > > from a
> > > table like below? (I cant use the INDEX function since I dont know the
> > > column
> > > or row numbers in the actual table, only the names)
> > >
> > > A B C D F
> > >
> > > 1 Week 42 Week 43 Week 44 Week 45
> > > 2 Company 1 67% 71% 69% 81%
> > > 3 Company 2 45% 78% 79% 73%
> > > 4 Company 3 88% 67% 86% 74%
> > > 5 Company 4 97% 56% 77% 92%
> > > 6 Company 5 87% 55% 82% 69%
> > >
> > >
> > >
> > >
> > >
> >
> >
> >

Was this post helpful to you?

Top

Roger Govier

11/23/2006 7:06 AM PST

Hi

Glad you managed to work it out, and that Dave spotted my "fat fingered"
typing again!!!

--
Regards

Roger Govier

"Olle" <Olle@discussions.microsoft.com> wrote in message
news:C2AF27DA-130A-406A-9B11-EAF0701BA096@microsoft.com...
> Dont mind my last question, Solved it, thx. Right formula becomes:
>
> =INDEX($A$1:E$6;MATCH("Company 2";A1:A6;0);MATCH("Week 43";A1:E1;0))
>
> Regards
>
> Olle
>
> "Olle" wrote:
>
>> Thank you very much for your quick response!
>>
>> I tried your suggested formula and with some minor adjustments it
>> almost got
>> it to work. Using the below formula I don't get the "Company 5, Week
>> 45"
>> value, I get the "Company 4, Week 44" instead. How can that be?
>> Solution?
>>
>> =INDEX($A$1:E$6;MATCH("Company 5";A2:A6;0);MATCH("Week 45";B1:E1;0))
>>
>> Regars
>>
>> Olle
>>
>>
>>
>> "Roger Govier" wrote:
>>
>> > Hi
>> >
>> > Use the MATCH function as well as INDEX.
>> >
>> > =INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1:$1,0))
>> >
>> > Substitute the word Name and Week with the cell references holding
>> > the
>> > Company and Week Number or insert their text as "Company2" and
>> > "Week 44"
>> > for example.
>> >
>> > --
>> > Regards
>> >
>> > Roger Govier
>> >
>> >
>> > "Olle" <Olle@discussions.microsoft.com> wrote in message
>> > news:C260FDD6-032D-4DFF-B12B-45814081668C@microsoft.com...
>> > > What cellformula, using column and row names, return a specific
>> > > value
>> > > from a
>> > > table like below? (I cant use the INDEX function since I dont
>> > > know the
>> > > column
>> > > or row numbers in the actual table, only the names)
>> > >
>> > > A B C D F
>> > >
>> > > 1 Week 42 Week 43 Week 44 Week 45
>> > > 2 Company 1 67% 71% 69% 81%
>> > > 3 Company 2 45% 78% 79% 73%
>> > > 4 Company 3 88% 67% 86% 74%
>> > > 5 Company 4 97% 56% 77% 92%
>> > > 6 Company 5 87% 55% 82% 69%
>> > >
>> > >
>> > >
>> > >
>> > >
>> >
>> >
>> >

Was this post helpful to you?

Top

Dave Peterson

11/23/2006 5:58 AM PST

Roger tried to start in A1, but lost a colon and had some trouble with ()s:

=INDEX($A$1:E$6,MATCH(Name,$A:$A,0),MATCH(week,$1:$1,0))

or

=INDEX($A$1:E$6;MATCH("Company 5";A1:A6;0);MATCH("Week 45";A1:E1;0))

Olle wrote:
>
> Thank you very much for your quick response!
>
> I tried your suggested formula and with some minor adjustments it almost got
> it to work. Using the below formula I don't get the "Company 5, Week 45"
> value, I get the "Company 4, Week 44" instead. How can that be? Solution?
>
> =INDEX($A$1:E$6;MATCH("Company 5";A2:A6;0);MATCH("Week 45";B1:E1;0))
>
> Regars
>
> Olle
>
> "Roger Govier" wrote:
>
> > Hi
> >
> > Use the MATCH function as well as INDEX.
> >
> > =INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1:$1,0))
> >
> > Substitute the word Name and Week with the cell references holding the
> > Company and Week Number or insert their text as "Company2" and "Week 44"
> > for example.
> >
> > --
> > Regards
> >
> > Roger Govier
> >
> >
> > "Olle" <Olle@discussions.microsoft.com> wrote in message
> > news:C260FDD6-032D-4DFF-B12B-45814081668C@microsoft.com...
> > > What cellformula, using column and row names, return a specific value
> > > from a
> > > table like below? (I cant use the INDEX function since I dont know the
> > > column
> > > or row numbers in the actual table, only the names)
> > >
> > > A B C D F
> > >
> > > 1 Week 42 Week 43 Week 44 Week 45
> > > 2 Company 1 67% 71% 69% 81%
> > > 3 Company 2 45% 78% 79% 73%
> > > 4 Company 3 88% 67% 86% 74%
> > > 5 Company 4 97% 56% 77% 92%
> > > 6 Company 5 87% 55% 82% 69%
> > >
> > >
> > >
> > >
> > >
> >
> >
> >

--

Dave Peterson

Was this post helpful to you?

Top

Olle

11/23/2006 7:07 AM PST

Thanks Dave!

I managed to get it working using the example table with company y and week
x. However, when I try in my actual table, which is a pivottable, it don't
work. I get values but not the right ones.

Would you know how to extract at value from a corresponding pivottable?

Regards

Olle
"Dave Peterson" wrote:

> Roger tried to start in A1, but lost a colon and had some trouble with ()s:
>
> =INDEX($A$1:E$6,MATCH(Name,$A:$A,0),MATCH(week,$1:$1,0))
>
> or
>
> =INDEX($A$1:E$6;MATCH("Company 5";A1:A6;0);MATCH("Week 45";A1:E1;0))
>
>
>
> Olle wrote:
> >
> > Thank you very much for your quick response!
> >
> > I tried your suggested formula and with some minor adjustments it almost got
> > it to work. Using the below formula I don't get the "Company 5, Week 45"
> > value, I get the "Company 4, Week 44" instead. How can that be? Solution?
> >
> > =INDEX($A$1:E$6;MATCH("Company 5";A2:A6;0);MATCH("Week 45";B1:E1;0))
> >
> > Regars
> >
> > Olle
> >
> > "Roger Govier" wrote:
> >
> > > Hi
> > >
> > > Use the MATCH function as well as INDEX.
> > >
> > > =INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1:$1,0))
> > >
> > > Substitute the word Name and Week with the cell references holding the
> > > Company and Week Number or insert their text as "Company2" and "Week 44"
> > > for example.
> > >
> > > --
> > > Regards
> > >
> > > Roger Govier
> > >
> > >
> > > "Olle" <Olle@discussions.microsoft.com> wrote in message
> > > news:C260FDD6-032D-4DFF-B12B-45814081668C@microsoft.com...
> > > > What cellformula, using column and row names, return a specific value
> > > > from a
> > > > table like below? (I cant use the INDEX function since I dont know the
> > > > column
> > > > or row numbers in the actual table, only the names)
> > > >
> > > > A B C D F
> > > >
> > > > 1 Week 42 Week 43 Week 44 Week 45
> > > > 2 Company 1 67% 71% 69% 81%
> > > > 3 Company 2 45% 78% 79% 73%
> > > > 4 Company 3 88% 67% 86% 74%
> > > > 5 Company 4 97% 56% 77% 92%
> > > > 6 Company 5 87% 55% 82% 69%
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

Was this post helpful to you?

Top

Roger Govier

11/23/2006 9:58 AM PST

Hi

If you are extracting the data from a Pivot Table, use the GetPivotData
command which is analogous to using Index Match, but works specifically
on PT's.

Debra Dalgleish has some good help on how to use this at
http://www.contextures.com/xlPivot06.html

--
Regards

Roger Govier

"Olle" <Olle@discussions.microsoft.com> wrote in message
news:A1D7BA07-3CB8-4016-A87B-8235B88579E7@microsoft.com...
> Thanks Dave!
>
> I managed to get it working using the example table with company y and
> week
> x. However, when I try in my actual table, which is a pivottable, it
> don't
> work. I get values but not the right ones.
>
> Would you know how to extract at value from a corresponding
> pivottable?
>
> Regards
>
> Olle
> "Dave Peterson" wrote:
>
>> Roger tried to start in A1, but lost a colon and had some trouble
>> with ()s:
>>
>> =INDEX($A$1:E$6,MATCH(Name,$A:$A,0),MATCH(week,$1:$1,0))
>>
>> or
>>
>> =INDEX($A$1:E$6;MATCH("Company 5";A1:A6;0);MATCH("Week 45";A1:E1;0))
>>
>>
>>
>> Olle wrote:
>> >
>> > Thank you very much for your quick response!
>> >
>> > I tried your suggested formula and with some minor adjustments it
>> > almost got
>> > it to work. Using the below formula I don't get the "Company 5,
>> > Week 45"
>> > value, I get the "Company 4, Week 44" instead. How can that be?
>> > Solution?
>> >
>> > =INDEX($A$1:E$6;MATCH("Company 5";A2:A6;0);MATCH("Week
>> > 45";B1:E1;0))
>> >
>> > Regars
>> >
>> > Olle
>> >
>> > "Roger Govier" wrote:
>> >
>> > > Hi
>> > >
>> > > Use the MATCH function as well as INDEX.
>> > >
>> > > =INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1:$1,0))
>> > >
>> > > Substitute the word Name and Week with the cell references
>> > > holding the
>> > > Company and Week Number or insert their text as "Company2" and
>> > > "Week 44"
>> > > for example.
>> > >
>> > > --
>> > > Regards
>> > >
>> > > Roger Govier
>> > >
>> > >
>> > > "Olle" <Olle@discussions.microsoft.com> wrote in message
>> > > news:C260FDD6-032D-4DFF-B12B-45814081668C@microsoft.com...
>> > > > What cellformula, using column and row names, return a specific
>> > > > value
>> > > > from a
>> > > > table like below? (I cant use the INDEX function since I dont
>> > > > know the
>> > > > column
>> > > > or row numbers in the actual table, only the names)
>> > > >
>> > > > A B C D F
>> > > >
>> > > > 1 Week 42 Week 43 Week 44 Week 45
>> > > > 2 Company 1 67% 71% 69% 81%
>> > > > 3 Company 2 45% 78% 79% 73%
>> > > > 4 Company 3 88% 67% 86% 74%
>> > > > 5 Company 4 97% 56% 77% 92%
>> > > > 6 Company 5 87% 55% 82% 69%
>> > > >
>> > > >
>> > > >
>> > > >
>> > > >
>> > >
>> > >
>> > >
>>
>> --
>>
>> Dave Peterson
>>

Was this post helpful to you?

Top

dellosa

9/17/2009 3:23 AM PST

hi there,

need your help almost similar to this, i want to return a value from the
series on the right of the table, say.. my input would be the company and a
predefined % value, exctract the percentage from the right table equal or
less than input predefined value and return the name of the week on the
header.

i've been using index,match lookups, combination but seems not working,
thanks so much.

"Roger Govier" wrote:

> Hi
>
> If you are extracting the data from a Pivot Table, use the GetPivotData
> command which is analogous to using Index Match, but works specifically
> on PT's.
>
> Debra Dalgleish has some good help on how to use this at
> http://www.contextures.com/xlPivot06.html
>
> --
> Regards
>
> Roger Govier
>
>
> "Olle" <Olle@discussions.microsoft.com> wrote in message
> news:A1D7BA07-3CB8-4016-A87B-8235B88579E7@microsoft.com...
> > Thanks Dave!
> >
> > I managed to get it working using the example table with company y and
> > week
> > x. However, when I try in my actual table, which is a pivottable, it
> > don't
> > work. I get values but not the right ones.
> >
> > Would you know how to extract at value from a corresponding
> > pivottable?
> >
> > Regards
> >
> > Olle
> > "Dave Peterson" wrote:
> >
> >> Roger tried to start in A1, but lost a colon and had some trouble
> >> with ()s:
> >>
> >> =INDEX($A$1:E$6,MATCH(Name,$A:$A,0),MATCH(week,$1:$1,0))
> >>
> >> or
> >>
> >> =INDEX($A$1:E$6;MATCH("Company 5";A1:A6;0);MATCH("Week 45";A1:E1;0))
> >>
> >>
> >>
> >> Olle wrote:
> >> >
> >> > Thank you very much for your quick response!
> >> >
> >> > I tried your suggested formula and with some minor adjustments it
> >> > almost got
> >> > it to work. Using the below formula I don't get the "Company 5,
> >> > Week 45"
> >> > value, I get the "Company 4, Week 44" instead. How can that be?
> >> > Solution?
> >> >
> >> > =INDEX($A$1:E$6;MATCH("Company 5";A2:A6;0);MATCH("Week
> >> > 45";B1:E1;0))
> >> >
> >> > Regars
> >> >
> >> > Olle
> >> >
> >> > "Roger Govier" wrote:
> >> >
> >> > > Hi
> >> > >
> >> > > Use the MATCH function as well as INDEX.
> >> > >
> >> > > =INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1:$1,0))
> >> > >
> >> > > Substitute the word Name and Week with the cell references
> >> > > holding the
> >> > > Company and Week Number or insert their text as "Company2" and
> >> > > "Week 44"
> >> > > for example.
> >> > >
> >> > > --
> >> > > Regards
> >> > >
> >> > > Roger Govier
> >> > >
> >> > >
> >> > > "Olle" <Olle@discussions.microsoft.com> wrote in message
> >> > > news:C260FDD6-032D-4DFF-B12B-45814081668C@microsoft.com...
> >> > > > What cellformula, using column and row names, return a specific
> >> > > > value
> >> > > > from a
> >> > > > table like below? (I cant use the INDEX function since I dont
> >> > > > know the
> >> > > > column
> >> > > > or row numbers in the actual table, only the names)
> >> > > >
> >> > > > A B C D F
> >> > > >
> >> > > > 1 Week 42 Week 43 Week 44 Week 45
> >> > > > 2 Company 1 67% 71% 69% 81%
> >> > > > 3 Company 2 45% 78% 79% 73%
> >> > > > 4 Company 3 88% 67% 86% 74%
> >> > > > 5 Company 4 97% 56% 77% 92%
> >> > > > 6 Company 5 87% 55% 82% 69%
> >> > > >
> >> > > >
> >> > > >
> >> > > >
> >> > > >
> >> > >
> >> > >
> >> > >
> >>
> >> --
> >>
> >> Dave Peterson
> >>
>
>
>

Was this post helpful to you?

Top

	Return to Microsoft Communities		Notify me of replies