Lookup / find value in column which changes

[LinLin]

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

 

[T. Valko]

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,"mmmyy"),1,),0))

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

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

 

[LinLin]

That's the business!

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

 

[T. Valko]

You're welcome. Thanks for the feedback!