Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value,return column heading
I am trying to locate a matching value and return its columnheading.
Ex A5=vlaue to find in row5 to return column heading a4:z4 hlookup(a5,b5:z5,(return matching column heading a4:z4),0) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value,return column heading
Try INDEX(A4:Z4,1,MATCH(A5,B5:Z5))
-- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "MFM" wrote: I am trying to locate a matching value and return its columnheading. Ex A5=vlaue to find in row5 to return column heading a4:z4 hlookup(a5,b5:z5,(return matching column heading a4:z4),0) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value,return column heading
If your data will not extend past column Z:
=LEFT(ADDRESS(ROW(A5),COLUMN(A5)+MATCH(A5,B5:Z5,0) ,2)) If your data might extend past Z (say until AH5): =LEFT(ADDRESS(ROW(A5),COLUMN(A5)+MATCH(A5,B5:AH5,0 ),2),FIND("$",ADDRESS (ROW(A5),COLUMN(A5)+MATCH(A5,B5:AH5,0),2))-1) HTH Kostis Vezerides On Nov 13, 5:31*pm, MFM wrote: I am trying to locate a matching value and return its columnheading. * Ex A5=vlaue to find in row5 to return column heading a4:z4 hlookup(a5,b5:z5,(return matching column heading a4:z4),0) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value,return column heading
Thank you.
I actually have to first look up by date then apply the formula to an aray to locate the column reference and copy doen. Please see my example below. Thank for your help. This is killing me. IN C14 =VLOOKUP(a14,$a$2:$c$11,MATCH($b$14,$B$2:$e$14,0), FALSE) A B C D E SCHEDULE 1 DATE SALES MKTG GEN PROD 2 01/01/07 dh PL ST 3 01/02/07 MS HG DM RO 4 01/03/07 HG TW RO 5 01/04/07 MS HR HG RO 6 01/05/07 DM MG HG 7 01/06/07 SU MG 8 01/07/07 MG SU DH 9 01/08/07 DM DR RO 10 01/09/07 HG ES RO 11 01/10/07 ah TW GO 12 13 DATE STAFF DEPT Wk'd from Above 14 01/01/07 PL ?? 15 01/06/07 MG ?? 16 01/09/07 ES ?? 17 01/06/07 SU ?? 18 01/10/07 TW ?? 19 01/03/07 RO ?? 20 01/01/07 DH ?? 21 01/06/07 SU ?? 22 01/09/07 RO ?? Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "MFM" wrote: I am trying to locate a matching value and return its columnheading. Ex A5=vlaue to find in row5 to return column heading a4:z4 hlookup(a5,b5:z5,(return matching column heading a4:z4),0) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value,return column heading
Given your data layout this is a formula to use in C14 and copy down:
=INDEX($B$1:$E$1,MATCH(B14,INDEX($B$2:$E$11,MATCH( A14,$A$2:$A$11,0),0), 0)) HTH Kostis On Nov 13, 8:00*pm, MFM wrote: Thank you. I actually have to first look up by date then apply the formula to an aray to locate the column reference and copy doen. *Please see my example below. Thank for your help. *This is killing me. IN C14 =VLOOKUP(a14,$a$2:$c$11,MATCH($b$14,$B$2:$e$14,0), FALSE) * * * * A * * * B * * * C * * * D * * * E * * * * SCHEDULE * * * * * * * * * * * * * * * * 1 * * * DATE * *SALES * MKTG * *GEN * * PROD 2 * * * 01/01/07 * * * * * * * *dh * * *PL * * *ST 3 * * * 01/02/07 * * * *MS * * *HG * * *DM * * *RO 4 * * * 01/03/07 * * * * * * * *HG * * *TW * * *RO 5 * * * 01/04/07 * * * *MS * * *HR * * *HG * * *RO 6 * * * 01/05/07 * * * * * * * *DM * * *MG * * *HG 7 * * * 01/06/07 * * * * * * * * * * * *SU * * *MG 8 * * * 01/07/07 * * * * * * * *MG * * *SU * * *DH 9 * * * 01/08/07 * * * * * * * *DM * * *DR * * *RO 10 * * *01/09/07 * * * * * * * *HG * * *ES * * *RO 11 * * *01/10/07 * * * * * * * *ah * * *TW * * *GO 12 * * * * * * * * * * * * * * * * * * * 13 * * *DATE * *STAFF * DEPT Wk'd from Above * * * * * * 14 * * *01/01/07 * * * *PL * * *?? * * * * * * * 15 * * *01/06/07 * * * *MG * * *?? * * * * * * * 16 * * *01/09/07 * * * *ES * * *?? * * * * * * * 17 * * *01/06/07 * * * *SU * * *?? * * * * * * * 18 * * *01/10/07 * * * *TW * * *?? * * * * * * * 19 * * *01/03/07 * * * *RO * * *?? * * * * * * * 20 * * *01/01/07 * * * *DH * * *?? * * * * * * * 21 * * *01/06/07 * * * *SU * * *?? * * * * * * * 22 * * *01/09/07 * * * *RO * * *?? * * * * * * * Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. *Thanks. "MFM" wrote: I am trying to locate a matching value and return its columnheading. * Ex A5=vlaue to find in row5 to return column heading a4:z4 hlookup(a5,b5:z5,(return matching column heading a4:z4),0) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Value and Return Column Heading | Excel Worksheet Functions | |||
Return the value of a column heading | Excel Discussion (Misc queries) | |||
Return column heading | Excel Worksheet Functions | |||
Return Column Heading after lookup | Excel Worksheet Functions | |||
LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) |