Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 46
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 46
Default 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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
find last row value in column when using MATCH to find column Bouce Excel Worksheet Functions 6 February 6th 08 11:16 PM
Find something in column a then find if column B matches criteria Darrell_Sarrasin via OfficeKB.com Excel Discussion (Misc queries) 8 November 28th 07 10:40 PM
Find Column Number via Lookup Ryan[_2_] Excel Worksheet Functions 4 May 18th 07 03:07 PM
Find First Non blank cell than find column header and return that value Silver Rose Excel Worksheet Functions 10 April 30th 07 05:56 PM
Lookup Value and find Corresponding Value on another row same column martialtiger Excel Discussion (Misc queries) 4 November 8th 05 10:44 PM


All times are GMT +1. The time now is 08:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"