VLOOKUP for Excel tests: how it works, with examples
VLOOKUP is one of the functions people worry about most before an Excel test. It looks harder than it is. Once you know what its four parts mean, most VLOOKUP questions follow the same pattern.
What VLOOKUP does
VLOOKUP searches the first column of a table for a value, then returns something from another column in the same row. Think of finding a product code in a price list, then reading across to its price.
| Part | What it means | In the example below |
|---|---|---|
| lookup_value | What you're searching for | A2, a product code |
| table_array | The table to search, with the search column on the left | $F$2:$H$20 |
| col_index_num | Which column of that table to return, counting from 1 | 3, the third column |
| range_lookup | FALSE for an exact match | FALSE |
Together, that makes =VLOOKUP(A2, $F$2:$H$20, 3, FALSE). In plain words: find the code from A2 in the first column of the table, and return what's in the third column of that row.
The mistakes that cost marks
- Leaving off FALSE. VLOOKUP then assumes your first column is sorted and can return a wrong answer that looks right
- Forgetting absolute references. Without the $ signs, the table range moves when you copy the formula down
- Searching a column that isn't on the left. VLOOKUP only looks in the first column of the range you give it
- Counting columns from the start of the sheet. The column number counts from the left edge of your range
If you see #N/A
#N/A means VLOOKUP couldn't find the value. Check for a typo or an extra space, and whether a number has been stored as text in one of the columns. TRIM can clear extra spaces. If the value really might be missing, wrap the formula in IFERROR so the cell shows a message instead: =IFERROR(VLOOKUP(A2, $F$2:$H$20, 3, FALSE), "Not found").
Try writing a few from scratch without looking anything up. Under test conditions, what matters is whether you can build the formula from memory.
More guides
DeskReady is an independent practice tool, not affiliated with any software vendor or employer. Tests vary, so use this as a general guide.