Blog
How to Use VLOOKUP in Excel: Exact Match and Other Sheets
VLOOKUP searches the first column of a table for a value and returns a value from another column in the same row. In a distribution or manufacturing office, it is how an item number on an order becomes a description and a unit price, or a customer number becomes a sales rep and payment terms. Typing prices onto a 300-line order by hand takes an hour and gets a line or two wrong; one filled-down VLOOKUP does it in a minute. This guide covers how to use VLOOKUP in Excel from the four arguments to other sheets, price-break tables and the alternatives.
The four VLOOKUP arguments
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The Microsoft Learn reference for VLOOKUP defines them this way:
- lookup_value: the value to search for in the first column of the table, typed in or referenced from a cell, such as an item number or employee ID.
- table_array: two or more columns of data. The first column holds the values you search. Uppercase and lowercase text are treated as equal.
- col_index_num: which column of the range to return, counted inside the range, not by sheet letter. On a range C:F, column C is 1 and F is 4. A number below 1 returns #VALUE!, and one larger than the range width returns #REF!.
- range_lookup: FALSE (or 0) finds exact matches only. TRUE, or leaving it out, returns the next largest value below the lookup value when there is no exact match, and requires the first column to be sorted ascending.

The formula above looks up the ID in A4 on the Employee_List sheet and returns the fifth column, Country. For codes and IDs, the fourth argument should always be FALSE. Leaving it out does not raise an error for a missing code; it quietly returns a neighboring row's value.
Looking up values on another sheet or workbook
To search another sheet, put the sheet name and an exclamation point before the range. While typing the formula, click the other sheet's tab and drag the range, and Excel writes SheetName!Range for you. Sheet names with spaces need single quotes: 'Item Master'!$A$2:$D$500.

Here a lookup form returns the employee name from a separate list. Lock the range with F4 so every row searches the same block when you fill down.

Leave the lookup value relative, like A4, and each row finds its own ID. Lock it, like $A$4 in the first example, and every cell reads the same ID, which suits a form that spreads one record across several fields. To return a different field from the same range, change only the column number.

For another workbook, the file name goes in square brackets: =VLOOKUP(A2,'[Item Master.xlsx]Master'!$A:$D,3,FALSE). The last results remain when the source is closed, but renaming or moving the file breaks the link. A list you use every day is easier to manage as a sheet in the same file.
Let MATCH find the column number
A hard-coded column number breaks the day someone inserts a column in the master. Exports from an ERP change column layout more often than anyone expects. Put MATCH in the column-number slot and VLOOKUP finds the column by its header:
=VLOOKUP($B2,Master!$A:$H,MATCH("Unit Price",Master!$A$1:$H$1,0),FALSE)
According to the Microsoft Learn reference for MATCH, MATCH returns the relative position of an item in a range, and a match type of 0 finds the first value exactly equal to the lookup value. Wherever "Unit Price" moves, the formula follows.
The same trick fills several columns at once. Give your result columns the same headers as the master and use MATCH(D$1,Master!$A$1:$H$1,0). One formula, filled right and down, pulls the right field into every cell.
Approximate match for price breaks
Approximate match is for "which bracket does this fall in". A quantity discount table lists the start of each bracket in ascending order: 0 at 0%, 100 at 3%, 500 at 5%, 1,000 at 8%. With =VLOOKUP(D2,Discounts!$A$2:$B$5,2,TRUE), a quantity of 230 matches the largest value not above it, 100, and returns 3%. Unsorted brackets give wrong answers, and a quantity below the first bracket returns #N/A. Re-sort whenever you add a bracket. Everywhere else, use FALSE.
Wildcards and error handling
With exact match and a text lookup value, * stands for any sequence of characters and ? for one character. =VLOOKUP("*"&G2&"*",$A:$D,4,FALSE) returns the price of the first description containing G2. Put a tilde before a literal asterisk or question mark.
When nothing matches, VLOOKUP returns #N/A. To show a blank in a report, use =IFNA(VLOOKUP(…),""). IFNA hides only #N/A, so a broken range still shows #REF!. If the master has the same key twice, VLOOKUP returns the first match, so check keys for duplicates. Persistent #N/A errors usually come from formatting and spaces; work through the checklist below.
👉 VLOOKUP Not Working? How to Fix #N/A, #REF! and #VALUE!
Worked example: pricing an order at Lakeview Distribution
The inside sales coordinator at Lakeview Distribution collects customer orders in an Orders sheet with customer, item number and quantity. The same workbook has an Item Master sheet exported from the ERP (item number, description, pack size, unit price) and a Discounts sheet with quantity brackets. Today's order from Brightway Retail has 214 lines.
She types Description, Pack Size and Unit Price in D1:F1, matching the master's headers, and enters =VLOOKUP($B2,'Item Master'!$A:$D,MATCH(D$1,'Item Master'!$A$1:$D$1,0),FALSE) in D2. Filling it two columns right and 214 rows down prices the whole order. The lookup value locks only the column ($B2) and the header reference locks only the row (D$1), so each cell finds its own field.
Six lines return #N/A. Four have a trailing space after the item number, fixed with a TRIM helper column. Two are items launched this month and not yet in the master; she asks the product manager for prices and adds them.
Next, discounts. Column G gets =VLOOKUP(C2,Discounts!$A$2:$B$5,2,TRUE) and column H the extended price, =C2*F2*(1-G2). She spot-checks the three lines over 1,000 units against the table to confirm 8% landed. The job that used to take an afternoon of copying now takes twenty minutes, most of it spent on the six exceptions. Before sending the confirmation she pastes the price column as values, so a later change in the master cannot rewrite an order that is already confirmed.
VLOOKUP vs INDEX/MATCH vs XLOOKUP
| VLOOKUP | INDEX/MATCH | XLOOKUP | |
|---|---|---|---|
| Return a column to the left | No | Yes | Yes |
| Default match | Approximate | Exact with match type 0 | Exact |
| Survives inserted columns | No, unless MATCH supplies the number | Yes | Yes |
| Available in | All versions | All versions | Microsoft 365, Excel 2021+ |
VLOOKUP only searches the first column and returns columns to its right. To find an item number from a description that sits to its right, use =INDEX(Master!$A:$A,MATCH(G2,Master!$B:$B,0)). In Microsoft 365 and Excel 2021 or later, =XLOOKUP(G2,Master!$B:$B,Master!$A:$A,"Not found") is the simplest of all and sets the not-found text in the same formula. Excel 2019 and earlier show #NAME? for XLOOKUP, so use VLOOKUP or INDEX/MATCH in files you send to customers.
FAQ
Q. Can VLOOKUP return several columns at once?
Use matching headers and MATCH for the column number, then fill right. In Microsoft 365 you can also pass an array such as {2,3,4} as the column number to spill three columns from one formula.
Q. How do I look up a value based on two conditions, such as customer and item?
Add a helper column at the left of the price table with =A2&"|"&B2, and look up the same combination. With XLOOKUP, =XLOOKUP(G2&H2,A:A&B:B,C:C) works without a helper column.
Q. What is the difference between VLOOKUP and HLOOKUP?
VLOOKUP searches down the first column (V for vertical); HLOOKUP searches across the first row (H for horizontal). Use HLOOKUP when the keys run across a header row, such as monthly prices.
Q. The result looks like a number but will not add up.
The master stores it as text. Convert the source column with Text to Columns, or wrap the formula in VALUE.
How inline AI fills in an order
inline AI opens the order workbook and, when you say "pull description and unit price from Item Master and the discount from Discounts by item number", writes lookups with the right ranges and match types and sets aside the lines it could not match, such as trailing spaces or new items. With the AI OS and ARP, a purchase order that arrives by email is entered as a priced sales order, so filling in order sheets stops being a daily task.
Hand your documents and your company’s work to inline AI.
Keep a file open, say what you need, and AI edits it or drafts a new one. Add the company AI OS and AI runs the rest of the work, ERP included.
Book a demo




