VLOOKUP practice exercises: exact match, approximate match, and the errors in between

What VLOOKUP does, in one table

VLOOKUP searches the first column of a table for a value and returns something from the same row. The syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Three of the four arguments cause nearly every mistake.

Take a price list in A1:C6 with Code, Product and Price as headers, and the code P-300 in F1. The formula =VLOOKUP(F1,A2:C6,3,FALSE) looks for P-300 down column A and returns the third column of that row, the price. The column number counts from the left edge of the table you gave VLOOKUP, not from column A of the sheet. FALSE asks for an exact match; leave it out and VLOOKUP assumes TRUE, an approximate match, and returns the wrong row without raising an error.

Exact match VLOOKUP exercises

Start with the plain case, then put the lookup inside a larger calculation, which is how it appears in real order sheets, and finish by auditing a sheet where someone else got the column number wrong.

In the second exercise, an order line has a product code in B2 and a quantity in C2, and the price list sits in G2:H4. The line total is =VLOOKUP(B2,$G$2:$H$4,2,FALSE)*C2. The dollar signs lock the price list so the formula can be copied down the order; without them, row 3 would search G3:H5 and miss the first product.

1. Find the price Pro
2. Price list into an order Pro
3. The wrong price column Pro

In the audit exercise, one of four lookups returns a category name instead of a price, because its third argument says 2 where it should say 3. Text in a numeric column is the fastest tell that the column index is wrong.

Approximate match: when TRUE is the right answer

Approximate match is a different tool, built for tiers. Given a sorted list of thresholds, VLOOKUP with TRUE returns the row of the largest value that is less than or equal to the value you looked up.

A tax bracket table in E2:F5 has starting incomes 0, 10,000, 40,000 and 90,000 in column E and rates of 10, 15, 25 and 32 percent in column F. For an income of 35,000 in B2, =VLOOKUP(B2,$E$2:$F$5,2,TRUE) finds the last threshold not above 35,000, which is 10,000, and returns 15 percent. For 95,000 it returns 32 percent. A fifth bracket means adding a row, not editing a formula.

• The first column must be sorted ascending. An unsorted table does not produce an error; it produces a wrong rate that looks plausible.
• A value below the first threshold returns #N/A, so start the table at zero or at the lowest possible value.
• The same pattern handles commission tiers, shipping bands, letter grades and volume discounts. Each row must show where its band starts, not where it ends.

1. Tax bracket lookup Pro
2. Shipping tiers Pro
3. Letter grades Pro

The IFS and nested IF exercises solve a grading scale the long way; past three tiers, a lookup table is shorter and easier to change.

Fixing #N/A: the VLOOKUP error exercises

#N/A from an exact-match VLOOKUP means one thing: the value was not found in the first column. The reason decides the fix.

• The value really is missing. Wrap the lookup: =IFERROR(VLOOKUP(F1,A2:C6,3,FALSE),"Not found"). IFNA does the same but only catches #N/A, so a real #REF! still shows. Wrap only the lookup, not the whole calculation, or you hide real mistakes.
• Stray spaces. "Ana " with a trailing space does not match "Ana". Look up TRIM(A2) instead of A2: =B2*VLOOKUP(TRIM(A2),$E$2:$F$5,2,FALSE). TRIM removes leading and trailing spaces and collapses repeated spaces inside the text.
• Text that looks like a number. A code stored as text in one table and as a number in the other never matches. Convert one side with VALUE, or multiply by 1, or use TEXT to turn the number into matching text.
• The lookup column is not the first column of the table. VLOOKUP only searches the leftmost column of table_array. If the codes sit in column C, the table must start at C, or you need INDEX and MATCH.

1. No more #DIV/0! Pro
2. Lookups that fail on stray spaces Pro
3. Numbers stored as text Pro

The commission exercise is a support ticket in miniature: the names look identical, most reps show #N/A, and the fix is one function inside the lookup.

When VLOOKUP cannot do it: INDEX MATCH and XLOOKUP practice

VLOOKUP cannot return a column to the left of the one it searches, and its column number breaks when someone inserts a column inside the table. Two alternatives remove both limits.

INDEX and MATCH split the job in two. MATCH(F1,C2:C6,0) returns the position of the name in column C as a number; INDEX(A2:A6, that number) returns the item at that position from column A. Together: =INDEX(A2:A6,MATCH(F1,C2:C6,0)). Because the search and return columns are named separately, direction does not matter and inserted columns shift nothing. A second MATCH for the column gives a two-way lookup on a grid: =INDEX(B3:D5,MATCH(G1,A3:A5,0),MATCH(G2,B2:D2,0)).

XLOOKUP, available in Excel 365 and Excel 2021, does the same in one function and defaults to exact match. =XLOOKUP(F1,B2:B5,A2:A5) returns from the left with no trick, and =XLOOKUP(F1,A2:A5,B2:B5,"Unknown code") handles the not-found case without IFERROR. If the file will be opened in Excel 2019 or older, INDEX and MATCH is the safe choice.

1. Look left with INDEX and MATCH Pro
2. Finishing position Pro
3. Price grid Pro
4. Return from the left Pro
5. When the code does not exist Pro

Do the MATCH exercise on its own first. Seeing MATCH return a bare position is what makes INDEX MATCH stop feeling like a memorized incantation.

VLOOKUP interview questions, with short answers

Excel assessments for analyst, finance and operations roles lean on lookups more than any other topic. These are the questions that come up.

• What does the fourth argument do? FALSE forces an exact match and returns #N/A if the value is missing. TRUE, or omitting it, gives an approximate match that needs a sorted first column. Type FALSE unless you want tiers.
• Can VLOOKUP look to the left? No. It always searches the first column of the table, so the return column must be to its right. Use INDEX and MATCH or XLOOKUP.
• What happens with duplicate keys? VLOOKUP returns the first match from the top and ignores the rest. If duplicates are possible, deduplicate the table, or use SUMIFS when you want a total.
• Why does my VLOOKUP return the wrong value with no error? Almost always approximate match on an unsorted table, or a column index pointing at the neighboring column. Check the fourth argument first, then the third.
• Is VLOOKUP case-sensitive? No. "abc" matches "ABC". Wildcards do work with exact match: "P-3*" matches the first code starting with P-3.

If you are preparing for a timed assessment, the page at /excel-practice-test lists the other topics that sit alongside lookups: SUMIFS, IF logic, text cleaning and date math.

How to practice VLOOKUP so it sticks

• Break a working formula on purpose: change the column number, remove the FALSE, add a space to a key. Knowing what each failure looks like is how you debug someone else's sheet in an interview.
• Switch on timed mode once the exercises pass, then finish with the Find the error level. Spotting a wrong lookup in a finished sheet is a different skill from writing one.