Lookup / find value in column which changes
 

I need to find a value which is always in Row 2 (for example), but it may be
in Column 1 or it may be in Column 100.
The column changes because sometimes Oct-08 is the 1st month of my data, or
it might be the 100th month of my data (if you get my drift) depending on
which report I'm looking at.
I can use the label Oct-08 as the criteria for the search. So Oct-08 may be
Column 1 in Report A, but it might be Column 100 in Report B.

Can anyone suggest a lookup function so I can obtain the value of Oct-08 in
a number of different reports?

thanks everyone

Lookup / find value in column which changes
 

Assume you have column headers in row 1 from B1:IV1 like Oct-08, Nov-08,
Dec-08 (true Excel dates formatted as mmm-yy)

You want to find the value in row 2, B2:IV1 for Oct-08. This one works for
either text or numeric values.

=INDEX(B2:IV2,MATCH("Oct08",INDEX(TEXT(B1:IV1,"mmm yy"),1,),0))

If the value to be returned in always numeric, here's another way:

=SUMPRODUCT(--(TEXT(B1:IV1,"mmmyy")="Oct08"),B2:IV2)

--
Biff
Microsoft Excel MVP


"LinLin" wrote in message
...
I need to find a value which is always in Row 2 (for example), but it may
be
in Column 1 or it may be in Column 100.
The column changes because sometimes Oct-08 is the 1st month of my data,
or
it might be the 100th month of my data (if you get my drift) depending on
which report I'm looking at.
I can use the label Oct-08 as the criteria for the search. So Oct-08 may
be
Column 1 in Report A, but it might be Column 100 in Report B.

Can anyone suggest a lookup function so I can obtain the value of Oct-08
in
a number of different reports?

thanks everyone

Lookup / find value in column which changes
 

That's the business!

I had a go at Match and Index but just ended up with a headache!
Many thanks!

"T. Valko" wrote:

Assume you have column headers in row 1 from B1:IV1 like Oct-08, Nov-08,
Dec-08 (true Excel dates formatted as mmm-yy)

You want to find the value in row 2, B2:IV1 for Oct-08. This one works for
either text or numeric values.

=INDEX(B2:IV2,MATCH("Oct08",INDEX(TEXT(B1:IV1,"mmm yy"),1,),0))

If the value to be returned in always numeric, here's another way:

=SUMPRODUCT(--(TEXT(B1:IV1,"mmmyy")="Oct08"),B2:IV2)

--
Biff
Microsoft Excel MVP


"LinLin" wrote in message
...
I need to find a value which is always in Row 2 (for example), but it may
be
in Column 1 or it may be in Column 100.
The column changes because sometimes Oct-08 is the 1st month of my data,
or
it might be the 100th month of my data (if you get my drift) depending on
which report I'm looking at.
I can use the label Oct-08 as the criteria for the search. So Oct-08 may
be
Column 1 in Report A, but it might be Column 100 in Report B.

Can anyone suggest a lookup function so I can obtain the value of Oct-08
in
a number of different reports?

thanks everyone

Lookup / find value in column which changes
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"LinLin" wrote in message
...
That's the business!

I had a go at Match and Index but just ended up with a headache!
Many thanks!

"T. Valko" wrote:

Assume you have column headers in row 1 from B1:IV1 like Oct-08, Nov-08,
Dec-08 (true Excel dates formatted as mmm-yy)

You want to find the value in row 2, B2:IV1 for Oct-08. This one works
for
either text or numeric values.

=INDEX(B2:IV2,MATCH("Oct08",INDEX(TEXT(B1:IV1,"mmm yy"),1,),0))

If the value to be returned in always numeric, here's another way:

=SUMPRODUCT(--(TEXT(B1:IV1,"mmmyy")="Oct08"),B2:IV2)

--
Biff
Microsoft Excel MVP


"LinLin" wrote in message
...
I need to find a value which is always in Row 2 (for example), but it
may
be
in Column 1 or it may be in Column 100.
The column changes because sometimes Oct-08 is the 1st month of my
data,
or
it might be the 100th month of my data (if you get my drift) depending
on
which report I'm looking at.
I can use the label Oct-08 as the criteria for the search. So Oct-08
may
be
Column 1 in Report A, but it might be Column 100 in Report B.

Can anyone suggest a lookup function so I can obtain the value of
Oct-08
in
a number of different reports?

thanks everyone