Return column name for cell value

**Kheiron** · 10-27-2014, 05:24 PM

I have a matrix of required training for job titles. The titles of the jobs are down column 2 of Sheet1. The required training titles are across row 1 as a header row. Required training is indicated by an "X" in the corresponding cell of "Job Title" and "Training Title."

I have made a second sheet that I created a drop down list of job titles, referencing the titles in sheet 1. In the cells underneath that job title, I want to automatically display the required training titles once I choose a job.

Most of the info I have found refers to looking up a cell value based on row and column. But not column name based on row name and cell value. If anyone has any info on how I should write this, please let me know. I'd appreciate anything I can get.

**protonLeah** · 10-27-2014, 05:30 PM

To attach a Workbook
(please do not post pictures or links to worksheets)

Click Advanced (next to quick post),
Scroll down until you see "Manage Attachments",
Click that then select "add files" (top right corner).
Click "Select Files" find your file, click "open" click "upload"
Once the upload is completed the file name will appear below the input boxes in this window.
Click "Done" at bottom right to close the Attachment Manager.
Click "Submit Reply"

**Kheiron** · 10-27-2014, 05:37 PM

Apache Training Matrix.xlsx

Thank you for the help.

**protonLeah** · 10-28-2014, 01:34 AM

Please Login or Register  to view this content.

copied in rows 2 = 28. Notice you will have to use a filter to hide blank rows.
"JobTitles" is a named range in matrix column B2:196

MATCH($A$1,JobTitles,0) returns the matrix row where the Data Val item is located.
ROW()+1, returns the row that the formula itself is in and points to the matrix column. The formulas are in rows 2 - 28, so the row 2 formula will look in column 3 ("C"), etc.
So, if you select "Blasting Crew - Sand" match returns row 12. The INDEX(Table1,MATCH($A$1,JobTitles,0),ROW()+1) in A2 will check row 12 column 3 of the matrix for "XX", if yes then INDEX('Apache Training Matrix'!$C$1:$AC$1,1,ROW()-1), will return the contents of first cell in the array, C1, i.e., "Job Specific...'

**Kheiron** · 10-28-2014, 03:20 PM

Thank you for the help. I do have a question though. Apache is the company that you saw their existing required training matrix. I have other companies that I need to do the same thing for. I figured it would be as easy as changing a few values and references in the formula. The new matrix I am referencing goes to AE instead of AC. When it goes beyond to 29 rows or more of training requirements, I get #REF! errors. I don't see anything indicating 1-28 in the formula. Why wouldn't it just extend out and get the data?

**protonLeah** · 10-28-2014, 04:44 PM

Your Table1 has range A3:AC197.You will have to modify the table to include two more columns: A3:AE197, or create a new tablex and update the formulas.