VLOOKUP Interview Questions and Answers
By ExcelReady TeamUpdated 5 min read
The short answer
VLOOKUP interview questions usually ask you to explain the four arguments, the difference between FALSE (exact match) and TRUE (approximate match), why VLOOKUP returns #N/A or a wrong value, why it can't look left, how to lock the table with $, and when to use XLOOKUP or INDEX MATCH instead. Answer each with a short example.
Key takeaways
- VLOOKUP's four arguments are lookup value, table, column number and match type; FALSE means exact match.
- TRUE (approximate match) needs the first column sorted A to Z, and it's meant for bands like tax or commission tiers.
- #N/A means not found; a wrong value usually means an approximate match on unsorted data.
- VLOOKUP can't look left and breaks when columns move; INDEX MATCH and XLOOKUP fix both.
On this page+
VLOOKUP interview questions are some of the most common in any Excel interview, because VLOOKUP shows quickly whether someone has really used Excel. Interviewers rarely stop at "what does it do?". They ask why it returns #N/A, what TRUE means and what to do when it can't look left. This guide gives thirteen questions with exact answers, worked on two small tables you can type in.
The sample data#
Most answers use this staff table in A1:D5:
| ID | Name | Dept | Salary |
|---|---|---|---|
| E01 | Priya | HR | 52000 |
| E02 | Tom | IT | 68000 |
| E03 | Lina | Sales | 47000 |
| E04 | Raj | IT | 71000 |
Basic VLOOKUP questions (1–3)#
These check that you know the syntax exactly.
1. What does VLOOKUP do? VLOOKUP searches the first column of a table for a value and returns a value from another column in the same row. The V stands for vertical: it searches down a column.
2. What are its four arguments?
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Argument | Meaning | Example |
|---|---|---|
| lookup_value | What to find | "E03" |
| table_array | Where to look; the lookup column must be first | A2:D5 |
| col_index_num | Which column of the table to return | 4 |
| range_lookup | FALSE for exact, TRUE for approximate | FALSE |
=VLOOKUP("E03",A2:D5,4,FALSE) returns 47000, Lina's salary. See the VLOOKUP function guide.
3. What happens if you leave out the last argument? VLOOKUP uses TRUE, an approximate match. On an unsorted table this can quietly return the wrong row. Always type FALSE when you want an exact match.
Match type questions (4–5)#
The difference between TRUE and FALSE is a favourite follow-up. The article on VLOOKUP TRUE vs FALSE covers it in depth.
4. When would you use TRUE? For bands or tiers: commission rates, tax brackets, grade boundaries. Given this tier table:
| Min Sales | Rate |
|---|---|
| 0 | 0% |
| 10000 | 3% |
| 25000 | 5% |
| 50000 | 7% |
=VLOOKUP(32000,A2:B5,2,TRUE) returns 5%. TRUE finds the largest minimum that is less than or equal to 32000, which is 25000.
5. What does TRUE need to work? The first column must be sorted smallest to largest. If it isn't, VLOOKUP can return a wrong answer without any error. A value smaller than the first row (here, a negative number) returns #N/A.
Error questions (6–8)#
Interviewers love asking how you'd fix a broken lookup.
6. Why does VLOOKUP return #N/A? #N/A means "not found". With FALSE, common causes are:
- a typo or extra spaces ("E03 " doesn't match "E03");
- a number stored as text in one place and as a number in the other;
- a table range that doesn't start at the lookup column;
- the value really isn't there.
The guide on why VLOOKUP is not working walks through each fix.
7. Why does VLOOKUP return the wrong value? Usually because the match type is TRUE (or missing) on unsorted data, or the column number points at the wrong column. Another cause is duplicate IDs: VLOOKUP always returns the first match. See why VLOOKUP returns the wrong value.
8. How do you show a message instead of #N/A? Wrap it in IFERROR or IFNA:
=IFNA(VLOOKUP("E09",A2:D5,4,FALSE),"Not found")returns Not found. IFNA catches only #N/A, so other mistakes, like a #REF! from a bad column number, still show.
Limits and workarounds (9–13)#
These separate confident users from beginners.
9. Can VLOOKUP look to the left?
No. It only returns columns to the right of the lookup column. To find the ID for "Raj", use =INDEX(A2:A5,MATCH("Raj",B2:B5,0)), which returns E04, or XLOOKUP in Excel 2021 or Microsoft 365: =XLOOKUP("Raj",B2:B5,A2:A5).
10. Why should you lock the table with $?
When you fill a VLOOKUP down, a relative table like A2:D5 slides to A3:D6, then A4:D7, and starts missing rows. $A$2:$D$5 keeps it fixed. Press F4 on Windows (Mac: ⌘T) to add the $ signs.
11. What happens when someone inserts a column?
The column number is typed in, so it doesn't update. If a column is inserted between Name and Salary, 4 now returns the new column. INDEX MATCH and XLOOKUP point at the return column itself, so they keep working.
12. Can VLOOKUP match on two columns?
Not directly, because it searches one column. The usual workaround is a helper column that joins the two values, such as =B2&"|"&C2, so you can look up "Raj|IT". Because VLOOKUP searches the first column of its table, the helper column has to sit to the left of the values you want back. The | separator stops different pairs from joining into the same text. In Excel 2021 or Microsoft 365, XLOOKUP can do it without a helper column by multiplying two conditions.
13. Can VLOOKUP do partial matches?
Yes, with wildcards and FALSE. =VLOOKUP("Li*",B2:D5,3,FALSE) finds the first name starting with "Li" and returns 47000. The * stands for any characters and ? for exactly one.
How to answer VLOOKUP questions in an interview#
Say what it does, give the four arguments in order, and mention FALSE without being asked. Then show you know its limits and the alternatives. Interviewers often follow a correct answer with "and what if it returns #N/A?", so have the causes ready. If you're asked to type one, read the formula back aloud as you go: "find E03, in this table, return column 4, exact match". For the basics around it, see the Excel interview questions for freshers, and for the wider list, the 25 Excel interview questions.
Practise it#
VLOOKUP questions are easy once your fingers know the formula. Practise VLOOKUP in a live spreadsheet: you type the formula, and it checks it instantly. Then practise approximate match for tiers and fix #N/A and wrong-value lookups. The exercise on this page is a commission-tier lookup to start with.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
What commission rate applies to sales of 32000? Click E2 and use VLOOKUP with an approximate match (TRUE) to find D2 in the tier table A2:B5 and return the rate from column 2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rate | Rate | ||||
| 2 | ||||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Tip: With TRUE, VLOOKUP finds the largest minimum that is less than or equal to 32000.
Practise this in a lesson
Frequently asked questions
What are the 4 arguments of VLOOKUP?
The lookup value (what to find), the table array (where to look, with the lookup column first), the column index number (which column to return), and the range lookup (FALSE for an exact match, TRUE for an approximate match).
What is the difference between TRUE and FALSE in VLOOKUP?
FALSE finds an exact match and returns #N/A if there isn't one. TRUE finds the largest value that is less than or equal to the lookup value, so it needs the first column sorted in ascending order and is used for bands and tiers.
Can VLOOKUP look to the left?
No. VLOOKUP always searches the first column of the table and returns a column to its right. To look left, use INDEX MATCH or XLOOKUP.
Why does VLOOKUP return #N/A when the value is there?
Usually because of hidden differences: extra spaces, a number stored as text in one table and as a number in the other, or a table range that doesn't start at the lookup column. Clean the data with TRIM or VALUE and check the range.