SOLVED

lookup data based on column header

%3CLINGO-SUB%20id%3D%22lingo-sub-2773614%22%20slang%3D%22en-US%22%3Elookup%20data%20based%20on%20column%20header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2773614%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20maser%20schedule%20that%20I%20will%20be%20using%20to%20build%20daily%20schedules%20from.%26nbsp%3B%20There%20are%20dates%20as%20column%20headers%20and%20employees%20names%20at%20the%20beginning%20of%20each%20row.%26nbsp%3B%20What%20I%20wanted%20to%20do%20is%20enter%20a%20date%20and%20shift%2C%20then%20have%20excel%20look%20in%20that%20specific%20column%20for%20the%20shift%20listed%20and%20return%20the%20employees%20name%20on%20the%20far%20right.%20For%20some%20reason%20I%20have%20not%20been%20able%20to%20get%20the%20index%20-%20match%20to%20pull%20the%20correct%20name.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20Excel%202016%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2773614%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2773664%22%20slang%3D%22en-US%22%3ERe%3A%20lookup%20data%20based%20on%20column%20header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2773664%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1163240%22%20target%3D%22_blank%22%3E%40c_shelby1030%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(A8%3AA17%2CMATCH(B2%2CINDEX(B8%3AAE17%2C0%2CMATCH(B1%2CB7%3AAE7%2C0))%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi All,  

 

I have a maser schedule that I will be using to build daily schedules from.  There are dates as column headers and employees names at the beginning of each row.  What I wanted to do is enter a date and shift, then have excel look in that specific column for the shift listed and return the employees name on the far right. For some reason I have not been able to get the index - match to pull the correct name.  

 

I am using Excel 2016

3 Replies
best response confirmed by c_shelby1030 (New Contributor)
Solution

@c_shelby1030 

Use

=INDEX(A8:A17,MATCH(B2,INDEX(B8:AE17,0,MATCH(B1,B7:AE7,0)),0))
Thank you so much, works perfectly!!! This has been driving me crazy!!!

@c_shelby1030 

Another possible formula:

=INDEX(A:A,SUMPRODUCT((B7:AE7=B1)*(B8:AE17=B2)*ROW(A8:A17)))