Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find last row value in column when using MATCH to find column | Excel Worksheet Functions | |||
Find something in column a then find if column B matches criteria | Excel Discussion (Misc queries) | |||
Find Column Number via Lookup | Excel Worksheet Functions | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Lookup Value and find Corresponding Value on another row same column | Excel Discussion (Misc queries) |