VLOOKUP Examples for Practice: Learn Excel Lookup Skills

For a first VLOOKUP practice problem, put product IDs in A2:A6, prices in B2:B6, and the lookup ID P104 in E2; enter =VLOOKUP(E2,$A$2:$B$6,2,FALSE) in F2. It returns 18.50. Start with exact-match examples like this, then practise choosing a return column, approximate matches, wildcards, and handling missing results.

Why practise VLOOKUP?

VLOOKUP finds a value in the first column of a selected table range and returns a value from another column on the same row. It is useful for joining information held in separate lists: for example, finding a product price from its code, a department from an employee number, or a category from a score. Practising with a small, readable table makes the logic easier to see before you apply it to a large workbook.

The function name refers to a vertical lookup: Excel searches down a column. Its four arguments tell Excel what to find, where to search, which column to return, and whether to use an exact or approximate match. For most everyday lookups, use an exact match by entering FALSE as the last argument. Leaving that argument out can make Excel use approximate matching, which may return a plausible but incorrect result if the table is not arranged as required.

VLOOKUP is available in Microsoft Excel for Microsoft 365 on Windows and Mac. The examples below use standard worksheet formulas and work in Excel for the web as well. The main differences between platforms are interface details rather than the formula logic.

Set up a practice table

Enter this sample list in cells A1:C6. The ID is in the first column, which is important: VLOOKUP must search the leftmost column of the range you give it. The stock figures are included for a later exercise.

Product ID	Product	Stock	Price
P101	Notebook	32	3.50
P102	Pens	58	2.25
P103	Folder	21	1.75
P104	USB drive	14	18.50
P105	Calculator	9	12.00

To keep the first example straightforward, you can move or copy the price values into column B and put the product IDs in column A, matching the two-column layout in the opening formula. Alternatively, use the full A1:D6 table and return the fourth column. The table range must include both the search column and the result column, and the column number counts from the left edge of that selected range—not from the worksheet’s column letters.

For repeatable exercises, place a lookup value in a separate cell such as E2. That lets you change the code without editing the formula. Dollar signs in a reference such as $A$2:$D$6 lock the range when you copy the formula to another cell.

Example 1: Find an exact match

Use the two-column layout described at the start: product IDs in A2:A6, prices in B2:B6, and P104 in E2. Enter =VLOOKUP(E2,$A$2:$B$6,2,FALSE). The result is 18.50, the price listed on the row for P104.

The arguments work like this: E2 is the value to find; $A$2:$B$6 is the lookup table; 2 tells Excel to return the second column of that range; and FALSE requests an exact match. This is a strong default for IDs, names, and other labels because it does not treat nearby values as acceptable substitutes. Change E2 to P102 and the same formula returns 2.25.

Practise copying the formula down a short list of lookup IDs. Keep the table range absolute with dollar signs, but leave E2 relative so it changes to E3, E4, and so on. If a code is absent from the table, an exact-match VLOOKUP returns #N/A rather than guessing.

Example 2: Choose a different return column

VLOOKUP can return any column to the right of its search column. Suppose the product names are in B2:B6 and stock counts are in C2:C6, with USB drive typed in E2. Use =VLOOKUP(E2,$B$2:$C$6,2,FALSE). It returns 14, the stock count for that product.

Here the selected range begins in B, not A, so the product-name column is column 1 of the range and stock is column 2. If you selected A2:C6 instead, the name would be column 2 and stock would be column 3. This relative counting is a frequent source of mistakes: the number in the third argument is not necessarily the worksheet column number.

Try changing the return-column number to 1 while keeping the range B2:C6. Excel will return the matching product name, because the first column in that range contains names. VLOOKUP cannot search in one column and return a value from a column to its left. That limitation matters when planning a table range.

Example 3: Use approximate match for score bands

Approximate matching is useful when each result applies across a range, such as assigning a label based on a score. Create a sorted table with minimum scores in A2:A5 and labels in B2:B5: 0 is Beginner, 60 is Developing, 75 is Proficient, and 90 is Advanced. Put 82 in E2. With those values in place, enter =VLOOKUP(E2,$A$2:$B$5,2,TRUE). The result is Proficient because 75 is the largest threshold not greater than 82.

For approximate matching, the first column of the lookup range must be sorted in ascending order. Excel finds the largest threshold that is less than or equal to the lookup value. If the thresholds are unsorted, the answer may be wrong. A score below the first threshold can also return #N/A. Do not use TRUE simply because a table looks close enough; for unique IDs, use FALSE.

To practise, try scores of 58, 60, 74, 75, 89, and 90. They should return Beginner, Developing, Developing, Proficient, Proficient, and Advanced respectively. This helps demonstrate that the threshold marks the start of a band, rather than the only score that earns its label.

Example 4: Find a partial text match

Wildcards let an exact-match search find text that follows a pattern. In the product table, with IDs in A2:A6 and names in B2:B6, put the formula =VLOOKUP("P10*",$A$2:$B$6,2,FALSE) in a result cell. It returns Notebook, the name on the first row whose ID starts with P10. The asterisk represents any number of characters, including none.

A question mark is another wildcard and stands for one character. Wildcards are helpful when a code or label is incomplete, but they can match more than one row. VLOOKUP returns the first matching row it encounters, so use a pattern only when that first-match behaviour is suitable. For a reliable unique lookup, enter the full key and use FALSE.

Turn the examples into a practice routine

To build skill rather than just copy formulas, cover the answer column and solve these tasks using the sample data:

Look up the price for P101, then P105. Check that the answers are 3.50 and 12.00.
Look up the stock count for Pens. Check that the result is 58.
Try an ID that is not in the list, such as P199, and observe the #N/A result.
Use the score-band table to classify 74 and 90, then explain why each result belongs to its band.
Copy a lookup formula down several rows and confirm that the table range stays fixed while the lookup cell changes.

Before writing each formula, identify three things: the lookup key, the range whose first column contains that key, and the result column’s position within that range. Then decide whether the task needs exact matching or a sorted threshold. This small planning step prevents many errors and makes it easier to inspect a formula written by someone else.

Common VLOOKUP problems and fixes

#N/A usually means Excel could not find an exact match. Check for a typo, extra spaces, or different data types. A number stored as text may not match a numeric value, even if both display the same digits. If the source data may contain spaces, clean the data or use a helper column; do not assume that changing FALSE to TRUE is a safe fix.

#REF! commonly appears when the column index asks for a column beyond the selected range. For example, a range with two columns cannot return column 3. A wrong result without an error often points to a mistaken column index, an unintended approximate match, or duplicate keys. VLOOKUP returns the first matching row, so it does not report that a key appears more than once.

If a missing item should show a friendly message, wrap the lookup with IFNA. With IDs in A2:A6, prices in B2:B6, and P199 in E2, enter =IFNA(VLOOKUP(E2,$A$2:$B$6,2,FALSE),"Not found"). The displayed result is Not found. This hides only the #N/A error; other formula problems remain visible, which is useful while troubleshooting.

When you edit a copied formula, confirm that the range remains anchored and that its leftmost column is still the lookup column. Also verify that the final argument is explicitly FALSE for exact matches. These checks are more useful than trying random changes until the cell displays something.

VLOOKUP compared with XLOOKUP

VLOOKUP remains widely used and is often the right choice when maintaining an existing workbook or sharing a simple formula with users of older Excel versions. Its main constraints are that the lookup field must be at the left edge of the selected range and that the return column is specified by a number. Inserting or rearranging columns can make that number point somewhere unexpected.

XLOOKUP, available in Microsoft 365 and Excel 2021 or later, can search one range and return a value from a separate range, including a column to the left. It also uses exact match by default. For new workbooks in supported versions, XLOOKUP can be easier to read; VLOOKUP is still valuable to learn because many spreadsheets already use it. If you need to exchange a workbook with users on older Excel versions, check that their version supports any newer functions before replacing an established formula.

Frequently Asked Questions

What is the simplest VLOOKUP formula to practise?

Use an exact-match formula with a small two-column table: search for a code in the first column and return a value from the second. Include FALSE as the fourth argument so Excel looks for the exact code rather than using approximate matching.

Why does VLOOKUP return #N/A when the value is visible?

The displayed values may differ underneath because one is stored as text, there may be leading or trailing spaces, or the formula’s range may not include the row. Compare the cell contents and data types, and check that the lookup key is in the range’s first column.

Can VLOOKUP return a value from a column on the left?

No. VLOOKUP searches the leftmost column of its selected range and returns from a column to its right. Rearrange the data or use XLOOKUP in Microsoft 365 or Excel 2021 and later when a leftward return is needed.

Should I use TRUE or FALSE in VLOOKUP?

Use FALSE for exact matches such as product IDs and employee numbers. Use TRUE for approximate threshold lookups only when the first column is sorted in ascending order and you understand that Excel returns the closest lower threshold.

What happens if the lookup value appears more than once?

VLOOKUP returns the first matching row from top to bottom. If duplicates are possible, check or remove them, or use a method designed to return multiple matches rather than assuming VLOOKUP will choose the intended record.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.