Row data for specific column headers based on row ID

Question

I have a worksheet with a few hundred rows of employee data. At its most basic level it lists EmployeeID's, Name and lists the skills that they have.

What I have done is on another worksheet (in my Employee Lookup workbook) I have used a combination of the index and match functions to look up an EmployeeID that I enter and return the skill level from the employee skills data sheet.

What I want to do is try to simplify how the data is obtained (I have limited vba knowledge but some excel formula knowledge, hence nesting index match). The Employee Skills sheet has Column A with EmployeeIDs and Row A with skill names (and prior headers).

I can set up the Index/Match for each row but then I need the column numbers for each skill and look up that EmployeeID and return the skills (even if they are blank).

I wouldn't really bother with trying to figure out how to begin to code this but I would think there would be an easier way to do this than making an Index/Match formula for each referenced skill (with there being anywhere from ~15-50 skills per department when I assist with 4 departments).

The Index/Match functions would take a lot of time and already has for one group. I don't think what I am wanting can be done in Excel but unfortunately that is the data resource I am left with.

Example of spreadsheet layout:

Answer 1

Short Answer:

^{(A detailed & color-coded screenshot breaking down the formula is further down, or full-screen .PNG here.)}

Here's a solution using sample data similar to yours:

The "short version" of the formula in D16 is:

=INDEX($C$4:$I$8,MATCH($D$11,$A$4:$A$8,0),MATCH(C16,$C$3:$I$3,0))

The actual formula I used looks scarier but it's just the above formula twice (copy/pasted) along with an IF so that it will bring over a blank cell if the "source cell" is blank (like Jane's Partying score)...

=IF(INDEX($C$4:$I$8,MATCH($D$11,$A$4:$A$8,0),MATCH(C16,$C$3:$I$3,0))=0,"",INDEX($C$4:$I$8,MATCH($D$11,$A$4:$A$8,0),MATCH(C16,$C$3:$I$3,0)))

Once that formula is in D16, I just "filled down" (or copy/paste) to the other skills.

• Note that all cell references in the above formula are "Absolute" except for C16.

We want all cell ranges to stay the same even if we copy/paste (or fill) the cell to another cell... except for C16: that's what our lookups are based on so that once does need to change when we copy the cell elsewhere.

---

"Two-dimensional Index/Match" Example:

Download .xslx sample file:

To get a better idea of how it works, you can download the Excel file from my screenshot here to experiment, modify, and adapt as desired.

^{It's a direct-download link to a Macro-free .XSLX, hosted by Jumpshare, which allows you to view online but it doesn't like complex formulae, and the columnar formatting is messed up on some browsers (however you can also download from the online viewer with the DOWNLOAD button at the ↗top-right↗ of the viewer.}

---

INDEX & MATCH Function Documentation & More Examples:

• Microsoft (Office.com): MATCH and INDEX functions (with videos)

• Microsoft: Switch between relative, absolute, and mixed references

• ExcelJet: Two-way lookup with INDEX and MATCH

• MSDN: Performing Two-Dimensional Lookups

• mbaExcel: Tutorial: How to Decide Which Excel Lookup Formula to Use

• Stack Overflow: Index/Match with VBA

• ExcelTip: How To Use Index/Match for 2 Criteria with VBA

---

A Crash-Course on Absolute vs. Relative Cell References

Cell Reference — A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. _(Source)

By default, a cell reference is relative. For example, when you refer to cell A2 from cell C2, you are actually referring to a cell that is two columns to the left (C minus A), and in the same row (2). A formula that contains a relative cell reference changes as you copy it from one cell to another.

As an example, if you copy the formula =A2+B2 from cell C2 to D2, the formula in D2 adjusts to the right by one column and becomes =B2+C2. If you want to maintain the original cell reference in this example when you copy it, you make the cell reference absolute by preceding the columns (A and B) and row (2) with a dollar sign ($). Then, when you copy the formula =$A$2+$B$2 from C2 to D2, the formula stays exactly the same.

In less frequent cases, you may want to make a cell reference "mixed" by preceding either the column or the row value with a dollar sign to "lock" either the column or the row (for example, $A2 or B$3).

_(Source)

---

>

"Regional Afterthought":

While answering a question about Excel's regional differences, I wondered if $ is the international symbol for Absolute References, or whether the symbol varied "across the pond".

("Will Brexit change "U.K. Absolutes" from =€A€1 to =£A£1?!")

A quick search revealed that the $ is indeed the "worldwide" symbol used for Excel Absolutes — however some users (having never typed a $ before) had trouble locating it. Here's a link describing How to type the $ Dollar Sign on Non-American Keyboards...

---

Good luck with your project! Let me know if you have any questions...