Reputation: 83
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 EmployeeID
s 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.
Upvotes: 2
Views: 1035
Reputation: 21657
(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.
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.
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
mbaExcel: Tutorial: How to Decide Which Excel Lookup Formula to Use
Stack Overflow: Index/Match with VBA
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 cellC2
, you are actually referring to a cell that is two columns to the left (C
minusA
), 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 cellC2
toD2
, the formula inD2
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
andB
) and row (2) with a dollar sign ($
). Then, when you copy the formula=$A$2+$B$2
fromC2
toD2
, 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
orB$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...
Upvotes: 3