Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I look up which column a value is in?
I have a sheet which has team manager names in the top row as headings, with
a vertical list of their staff underneath in the same column I want to conduct some sort of lookup function which will search for a staff member then return their TM by looking in the top row of the particular column they appear in. Is this possible? I thought hlookup would be the answer but this only seems happy to look in the top row. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I look up which column a value is in?
Hi,
I have assumed you team managers are in row 1 (A1 to E1) and that your table is 10 rows deep and the name you are looking for is in f1. Try this array formula =INDEX(A1:E1,MIN(IF(A2:E10=F1,COLUMN(A2:E10)-MIN(COLUMN(A2:E10))+1))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "mrpleasant" wrote: I have a sheet which has team manager names in the top row as headings, with a vertical list of their staff underneath in the same column I want to conduct some sort of lookup function which will search for a staff member then return their TM by looking in the top row of the particular column they appear in. Is this possible? I thought hlookup would be the answer but this only seems happy to look in the top row. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I look up which column a value is in?
Thanks, that's exactly what I was looking for. Not sure how it works, but it
does! Just to complicate matters further, would there be a way of searching more than one sheet with the same function? "Mike H" wrote: Hi, I have assumed you team managers are in row 1 (A1 to E1) and that your table is 10 rows deep and the name you are looking for is in f1. Try this array formula =INDEX(A1:E1,MIN(IF(A2:E10=F1,COLUMN(A2:E10)-MIN(COLUMN(A2:E10))+1))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "mrpleasant" wrote: I have a sheet which has team manager names in the top row as headings, with a vertical list of their staff underneath in the same column I want to conduct some sort of lookup function which will search for a staff member then return their TM by looking in the top row of the particular column they appear in. Is this possible? I thought hlookup would be the answer but this only seems happy to look in the top row. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I look up which column a value is in?
Hi,
Thanks for the feedback, I'm gald that worked for you. Just to complicate matters further, would there be a way of searching more than one sheet with the same function? I'm not sure precisely what you mean but this now searches sheet 2 and returns the header from the sheet the formula is on =INDEX(A1:E1,MIN(IF(Sheet2!A2:E10=F1,COLUMN(Sheet2 !A2:E10)-MIN(COLUMN(Sheet2!A2:E10))+1))) Mike "mrpleasant" wrote: Thanks, that's exactly what I was looking for. Not sure how it works, but it does! Just to complicate matters further, would there be a way of searching more than one sheet with the same function? "Mike H" wrote: Hi, I have assumed you team managers are in row 1 (A1 to E1) and that your table is 10 rows deep and the name you are looking for is in f1. Try this array formula =INDEX(A1:E1,MIN(IF(A2:E10=F1,COLUMN(A2:E10)-MIN(COLUMN(A2:E10))+1))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "mrpleasant" wrote: I have a sheet which has team manager names in the top row as headings, with a vertical list of their staff underneath in the same column I want to conduct some sort of lookup function which will search for a staff member then return their TM by looking in the top row of the particular column they appear in. Is this possible? I thought hlookup would be the answer but this only seems happy to look in the top row. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I look up which column a value is in?
Hi again
Sorry, I should have made myself clearer. I want to enter a value, and this would search multiple sheets and return the value in Row 1 of that column on whichever sheet the name was on. i.e. Sheet 1: TM 1 TM 2 TM 3 TM 4 TM 5 Staff 1 Staff 6 Staff 11 Staff 16 Staff 21 Staff 2 Staff 7 Staff 12 Staff 17 Staff 22 Staff 3 Staff 8 Staff 13 Staff 18 Staff 23 Staff 4 Staff 9 Staff 14 Staff 19 Staff 24 Staff 5 Staff 10 Staff 15 Staff 20 Staff 25 Sheet 2: TM 6 TM 7 TM 8 TM 9 TM 10 Staff 26 Staff 31 Staff 36 Staff 41 Staff 46 Staff 27 Staff 32 Staff 37 Staff 42 Staff 47 Staff 28 Staff 33 Staff 38 Staff 43 Staff 48 Staff 29 Staff 34 Staff 39 Staff 44 Staff 49 Staff 30 Staff 35 Staff 40 Staff 45 Staff 50 So 'Staff 30' would return 'TM 6' Hope this makes sense! "Mike H" wrote: Hi, Thanks for the feedback, I'm gald that worked for you. Just to complicate matters further, would there be a way of searching more than one sheet with the same function? I'm not sure precisely what you mean but this now searches sheet 2 and returns the header from the sheet the formula is on =INDEX(A1:E1,MIN(IF(Sheet2!A2:E10=F1,COLUMN(Sheet2 !A2:E10)-MIN(COLUMN(Sheet2!A2:E10))+1))) Mike "mrpleasant" wrote: Thanks, that's exactly what I was looking for. Not sure how it works, but it does! Just to complicate matters further, would there be a way of searching more than one sheet with the same function? "Mike H" wrote: Hi, I have assumed you team managers are in row 1 (A1 to E1) and that your table is 10 rows deep and the name you are looking for is in f1. Try this array formula =INDEX(A1:E1,MIN(IF(A2:E10=F1,COLUMN(A2:E10)-MIN(COLUMN(A2:E10))+1))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "mrpleasant" wrote: I have a sheet which has team manager names in the top row as headings, with a vertical list of their staff underneath in the same column I want to conduct some sort of lookup function which will search for a staff member then return their TM by looking in the top row of the particular column they appear in. Is this possible? I thought hlookup would be the answer but this only seems happy to look in the top row. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I look up which column a value is in?
Hi,
Let's say that your data is arranged like this in range D8:G13 (including the headings). Enter 1,2,3,4 in D7:G7. Manager 1 Manager 2 Manager 3 Manager 4 A F T N S G Y M D H U Q J I OIn cell D14, enter A. In cell E14, you can use the following formula =CHOOSE(SUMPRODUCT(($D$9:$G$13=D14)*(D7:G7)),D8,E8 ,F8,G8) Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "mrpleasant" wrote in message ... I have a sheet which has team manager names in the top row as headings, with a vertical list of their staff underneath in the same column I want to conduct some sort of lookup function which will search for a staff member then return their TM by looking in the top row of the particular column they appear in. Is this possible? I thought hlookup would be the answer but this only seems happy to look in the top row. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I look up which column a value is in?
Hi,
I forgot to add that in the choose function, you can only specify upto 29 values I.e. only upto 29 managers -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "mrpleasant" wrote in message ... I have a sheet which has team manager names in the top row as headings, with a vertical list of their staff underneath in the same column I want to conduct some sort of lookup function which will search for a staff member then return their TM by looking in the top row of the particular column they appear in. Is this possible? I thought hlookup would be the answer but this only seems happy to look in the top row. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I look up which column a value is in?
Hi,
This now works for sheet 1 and sheet 2 and if the lookup isn't found an error message is returned =IF(COUNTIF(A2:E10,F1)0,INDEX(A1:D1,MIN(IF(A2:E10 =F1,COLUMN(A2:E10)-MIN(COLUMN(A2:E10))+1))),IF(COUNTIF(Sheet2!A2:E10, F1)=0,"Lookup not Found",INDEX(Sheet2!A1:D1,MIN(IF(Sheet2!A2:E10=F1, COLUMN(Sheet2!A2:E10)-MIN(COLUMN(Sheet2!A2:E10))+1))))) It's becoming a bit unweildy so to progress to more sheets then I'd resort to VB Once again array entered. Mike "mrpleasant" wrote: Hi again Sorry, I should have made myself clearer. I want to enter a value, and this would search multiple sheets and return the value in Row 1 of that column on whichever sheet the name was on. i.e. Sheet 1: TM 1 TM 2 TM 3 TM 4 TM 5 Staff 1 Staff 6 Staff 11 Staff 16 Staff 21 Staff 2 Staff 7 Staff 12 Staff 17 Staff 22 Staff 3 Staff 8 Staff 13 Staff 18 Staff 23 Staff 4 Staff 9 Staff 14 Staff 19 Staff 24 Staff 5 Staff 10 Staff 15 Staff 20 Staff 25 Sheet 2: TM 6 TM 7 TM 8 TM 9 TM 10 Staff 26 Staff 31 Staff 36 Staff 41 Staff 46 Staff 27 Staff 32 Staff 37 Staff 42 Staff 47 Staff 28 Staff 33 Staff 38 Staff 43 Staff 48 Staff 29 Staff 34 Staff 39 Staff 44 Staff 49 Staff 30 Staff 35 Staff 40 Staff 45 Staff 50 So 'Staff 30' would return 'TM 6' Hope this makes sense! "Mike H" wrote: Hi, Thanks for the feedback, I'm gald that worked for you. Just to complicate matters further, would there be a way of searching more than one sheet with the same function? I'm not sure precisely what you mean but this now searches sheet 2 and returns the header from the sheet the formula is on =INDEX(A1:E1,MIN(IF(Sheet2!A2:E10=F1,COLUMN(Sheet2 !A2:E10)-MIN(COLUMN(Sheet2!A2:E10))+1))) Mike "mrpleasant" wrote: Thanks, that's exactly what I was looking for. Not sure how it works, but it does! Just to complicate matters further, would there be a way of searching more than one sheet with the same function? "Mike H" wrote: Hi, I have assumed you team managers are in row 1 (A1 to E1) and that your table is 10 rows deep and the name you are looking for is in f1. Try this array formula =INDEX(A1:E1,MIN(IF(A2:E10=F1,COLUMN(A2:E10)-MIN(COLUMN(A2:E10))+1))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "mrpleasant" wrote: I have a sheet which has team manager names in the top row as headings, with a vertical list of their staff underneath in the same column I want to conduct some sort of lookup function which will search for a staff member then return their TM by looking in the top row of the particular column they appear in. Is this possible? I thought hlookup would be the answer but this only seems happy to look in the top row. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
divide column(x) by column(y) to give column(x/y) in excel? | New Users to Excel | |||
Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
Return text in Column A if Column B and Column K match | Excel Worksheet Functions | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
Divide Column A by Column B multiply Column C | Excel Worksheet Functions |