How to use the VLOOKUP function in Excel
Excel's VLOOKUP function can be used to speed up the completion of salary survey questionnaires. This article is a quick start guide on how to use the feature.
Excel's VLOOKUP function can be used to:
- Combine two sets of data;
- Check that data from one set is included in another set; or,
- Create dynamic content.
Cells, ranges and names
An Excel document primarily consists of cells which hold numbers or text. Every cell has a unique name based on its column letter and row number eg, A1. Groups of cells are called ranges. Ranges are defined by their top-left and bottom-right cells eg, A1:C4. These references are used in formulas (eg, SUM or VLOOKUP). You can assign "friendly" names to both cells and ranges to help you remember what they are for.
Completing an XpertHR salary survey questionnaire primarily involves combining two sets of data - your payroll data and our spreadsheet.
Combining two sets of data involves using a key - a piece of information that is both unique and present in both sets of data.
VLOOKUP takes a key, looks for it in a designated range, and - assuming a match is found - returns a value from the specified column in the same row.
In short, using VLOOKUP simply involves specifying a key, a range and a column.
Consider the example below. On the left is payroll data. On the right is a salary survey questionnaire with some IDs but no basic salaries:
The three pieces of information needed to use VLOOKUP are the key, the range and the column - all of which can be seen in the above example:
- Key - this is clearly "ID". It is unique and present in both data sets. We need to reference the keys used in the right hand table - cells D2, D3 and D4.
- Range - this is the payroll data from cell A2 to B11. It includes the key as the first column and then the desired column(s). Excel describes this as $A$2:$B$11.
- Column - the range has two columns, we want the second one. Excel needs a number: 2, in this case.
The syntax for the VLOOKUP function is:
=VLOOKUP(key, range, column, FALSE)
Replacing the key, range and column with the actual values from the example produces...
=VLOOKUP(D2, $A$2:$B$11, 2, FALSE)
...which should be typed into cell E2...
Now press Enter:
Summary of the above example:
There are several ways to complete the other cells, E3 and E4:
- Manually type the same formula into the remaining cells (very time consuming, risks typos - not recommended)
- Copy the E2 cell and paste into the remaining cells (less time consuming but not appropriate with more than a handful of cells - so better but not good)
- Use Excel's fill-down feature (see below)
VLOOKUP is useful but may sometimes produce problems.
- Cell with VLOOKUP formula displays #N/A!
Excel is telling you that it can't find the key in the first column of the range. #N/A! is short for 'Value Not Available Error'.
- Is the specified range correct? Ensure that the range (eg, D2:G500) includes all of the relevant cells.
- Have you copied or filled-down the VLOOKUP formula without ensuring that the range is locked with surrounding dollar signs? Use $D$2:$G$500 rather than D2:G500.
- Are the keys in the first column of the specified range? VLOOKUP is only ever going to match on the first column.
- Is the key value actually in the specified range? Manually check one or two keys, Ctrl + F is a useful tool here. If the key value is not in the range it's a data problem not an Excel problem.
- Do the keys in the range have the same format as the key specified in the VLOOKUP formula? Visually, Excel displays the text "1234" and the number 1234 in pretty much the same way - but for the purposes of VLOOKUP these are completely different things and cannot be matched.
- Cell with VLOOKUP formula displays #REF!
Excel is telling you that you're referring to something that it cannot find. #REF! is short for 'Invalid Cell Reference Error'.
- Check the key - is it present and correct in both the formula and the worksheet? Deleting rows and columns can cause problems.
- Check the range - is it correct? Typing it in again is worth a try.
- Check the column - have you entered a number greater than the count of the columns in the range? For example, asking for column 5 from a range A1:C4 (ie, a range with only 3 columns) will cause this error.
- Cell with VLOOKUP formula displays an unexpected value
This is likely to be a data issue.
- Check the range - is it correct? With lots of data floating around it is easy to lose track of what's what.
- Check the column - have you told VLOOKUP to return data from the correct column? Re-count the number of columns in the range and work out which one you need. Note that the first column in the range (containing the keys) is column 1.
- Check the contents of the range - does it contain duplicate keys? If the range contains duplicate keys (eg, 1234 is listed twice) VLOOKUP will return the value in the column for the first match it encounters. This may not be the match you wanted.
- Check the data that is the source of the range - has it come for the right place?
Tips and tricks
In Excel it is possible to reference cells from another spreadsheet. Guidance on using this feature is available here.
All of these following shortcuts are available across most windows applications. The F2 shortcut is particularly useful for activating and editing cells in an Excel document.
- Ctrl + F - find something in the current document
- Ctrl + H - find and replace something in the current document
- Ctrl + A - select all items (in some instances this shortcut can be used multiple times to progressively select more of the document)
- Ctrl + C - copy the selected item
- Ctrl + X - cut the selected item
- Ctrl + V - paste the copied/cut item
- Ctrl + Z - undo last action
- Ctrl + Y - re-do last action
- Ctrl + S - save the current document
- Esc - escape out of the current action (useful if you want to back out of editing a formula)
- Shift + ↑ or ↓ or ← or → - move in the indicated direction and select all cells along the way
- Ctrl + ↑ or ↓ or ← or → - move the cursor to the next breakpoint in the indicated direction (very useful for moving between blocks of content)
- F1 - open the help section
- F2 - activate / edit the current cell
There are also keyboard shortcuts that are specific to Excel.
- Ctrl + 1 - edit the format, font or border properties of a cell
- Ctrl + Shift + ↑ or ↓ or ← or → - move the cursor to the next breakpoint in the indicated direction, select all cells along the way
- Ctrl + Shift + End - move the cursor to the end of the document, select all cells along the way
- Ctrl + PageUp or PageDown - go to the next worksheet in the workbook
- F9 [when you are editing a formula] - selecting part of a formula and pressing F9 will make Excel calculate the selected part immediately and display the result within the formula. (Remember to used Esc afterwards if you don't want this change made permanent.)
- Ctrl + D - fill down (see below)
- Ctrl + R - fill right (see below)
There is an alternative to laboriously copying formulas from cell-to-cell. Simply select BOTH the cell with the formula to be copied AND the cells beneath…and then press Ctrl + D.
Fill-down works vertically. Another convenient method, fill-across, works horizontally and is invoked by selecting the relevant cells and then pressing Ctrl + R.