Lookup Job Code, return column header if cell has text

Question

I'm looking for a little bit of help learning about how to use vlookup and iferror formulas together.

I am working as a licensing specialist within the insurance industry. My job requires that I frequently access more than a dozen spreadsheets that are emailed to us on a weekly basis from our clients. I am working to develop tools that allow us to consolidate that information on-demand to be able to run reports. I'm having no trouble using the VLookup function to pull data from various spreadsheets using an unique identifier. I do need some help, however, with a trickier formula.

I have put together a spreadsheet that allows me to put in the ID of the representative and it returns various bits of information, such as their name, license numbers, job codes, etc. Now I need to come up with a formula that takes the value returned in the job code field and searches another spreadsheet to return what training they are required to complete based on their job code. This spreadsheet is about 2000 rows, and is formatted with the training names spanning across the top row, job codes listed in column "A" and the word "yes" in each column that matches a training that the job code is required to take.

It looks like this: https://i.imgur.com/71COmfF.png

I need the formula in my reports spreadsheet to lookup the job code using VLookup and then return the column header if the cell has text.

The data will be loaded into this spreadsheet: https://i.imgur.com/2CyFURt.png

Here is the Vlookup formula I am using to get the job code:

=VLOOKUP(B3,'.\[Weekly_HR_Report_040615.xlsx]HR'!$A:$Q,10,FALSE)

How can I pair that same kind of formula with something like an IfError formula to return the column header if there is a "Yes" in the cell of the row that matches their job code?

I know that I can work through it as an array using VBA, but I would prefer to just place a formula in each cell so that I can pass this spreadsheet to another college when I move to a different role in the future and they won't struggle too much with adding to it.

Thanks for any help that you might be able to provide! I really appreciate it!

CactusCake CactusCake · Accepted Answer · 2015-04-13T14:52:07

I would use INDEX and MATCH to cross reference and retrieve the titles where applicable:

=IF(INDEX('JobCode_Training.xlsx'!$A$1:$H$6,MATCH($H$5,'JobCode_Training.xlsx'!$A:$A,0),2)="Yes",INDEX('JobCode_Training.xlsx'!$A$1:$H$6,1,2),"")

=IF(INDEX('JobCode_Training.xlsx'!$A$1:$H$6,MATCH($H$5,'JobCode_Training.xlsx'!$A:$A,0),3)="Yes",INDEX('JobCode_Training.xlsx'!$A$1:$H$6,1,3),"")

=IF(INDEX('JobCode_Training.xlsx'!$A$1:$H$6,MATCH($H$5,'JobCode_Training.xlsx'!$A:$A,0),4)="Yes",INDEX('JobCode_Training.xlsx'!$A$1:$H$6,1,4),"")

etc.

I wasn't sure what your reference table is saved as, so you'll need to replace 'JobCode_Training.xlsx'! with whatever the correct path is.

Since you have multiple trainings that can apply to each jobcode, you'll either need to have multiple cells for the results, or you can concatenate them into one.

Lookup Job Code, return column header if cell has text

2 Answers