On this page · 9 sections
Summary. To use VLOOKUP in Excel, type =VLOOKUP(lookup_value, table_array, col_index_num, FALSE) in the cell where the answer should appear. For example, =VLOOKUP(F2,$A$2:$D$6,2,FALSE) finds the SKU in F2 in column A and returns the second column of the range. Use FALSE (or 0) for an exact match: without it, Excel uses an approximate match that expects a sorted first column and can return the wrong row. To look up data on another sheet, put the sheet name and an exclamation mark before the range, as in Products!$A$2:$D$6, and wrap names that contain spaces in single quotes, as in 'Price List'!$A$2:$D$6. Excel adds the quotes when you click the range. Most #N/A results come from a missing value, numbers stored as text, stray spaces or an approximate match on unsorted data, so fix the cause before adding IFNA. VLOOKUP works in Excel for Microsoft 365 on Windows and Mac, Excel 2024, 2021, 2019 and 2016, and Excel for the web. If every user has Microsoft 365 or Excel 2021 or later, XLOOKUP is the more flexible choice.
About this article. For anyone who builds or maintains Excel workbooks, including IT teams supporting shared templates. We checked the syntax, steps, shortcuts, error causes and version availability against Microsoft Support's Excel help, Microsoft Learn release notes and Microsoft's lifecycle pages on 15 November 2026, and recalculated every worked example. Labels can differ slightly between versions and update channels. Researched with AI assistance and reviewed by our editorial team.
VLOOKUP syntax: the four arguments
Microsoft gives the syntax as VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The first three arguments are required; the fourth is optional.
| Argument | What to enter | In this guide's example |
|---|---|---|
| lookup_value | The value to find: a cell reference or a value | F2, which holds P-102 |
| table_array | The range to search. Its first column holds the lookup value, and it must include the column you want back | A2:D6 |
| col_index_num | The column to return, counting the left-most column of table_array as 1 | 2 (Product) |
| range_lookup | FALSE or 0 for an exact match; TRUE, 1 or blank for an approximate match on sorted data | FALSE |
Sample data: a product list
Type this into a sheet named Products (double-click the sheet tab to rename it). Row 1 holds headings; the data runs from A2 to D6.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | SKU | Product | Price | Stock |
| 2 | P-101 | USB-C cable | 12.99 | 140 |
| 3 | P-102 | Wireless mouse | 24.50 | 65 |
| 4 | P-103 | Laptop stand | 39.00 | 22 |
| 5 | P-104 | HDMI adapter | 17.25 | 0 |
| 6 | P-105 | Webcam | 59.99 | 18 |
How to use VLOOKUP in Excel, step by step
These steps work in Excel for Windows, Mac and the web, checked against Excel for Microsoft 365 Current Channel Version 2610 (Build 20527.20196) and Excel for Mac 16.115, both released on 10 November 2026. The function is the same in Excel 2024, 2021, 2019 and 2016.
- In cell
F2, type the SKU you want to find:P-102.
- Select cell
G2, where the answer should appear.
- Type
=VLOOKUP(and Excel shows the argument list as a tooltip.
- Select
F2, then type a comma.
- Select
A2:D6and press F4 to change it to$A$2:$D$6, an absolute reference that stays put when you copy the formula. On a Mac, press Cmd+T or F4 (add Fn if the function key controls something else); F4 also works in Excel for the web.
- Type
,2,FALSE)and press Enter.
The finished formula is =VLOOKUP(F2,$A$2:$D$6,2,FALSE) and it returns Wireless mouse. Change the 2 to 3 and you get the price, 24.5; change it to 4 and you get the stock, 65.
To fill in boxes instead, select Formulas > Insert Function, search for VLOOKUP and complete the four fields; Excel describes each argument as you go. On a Mac, Shift+F3 opens the Formula Builder.
How to use VLOOKUP between two sheets
The usual job is pulling data from a reference list into a working sheet. Add a second sheet named Orders:
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Order ID | SKU | Price | Stock |
| 2 | SO-5001 | P-103 | (formula) | (formula) |
| 3 | SO-5002 | P-101 | (formula) | (formula) |
| 4 | SO-5003 | P-105 | (formula) | (formula) |
- On the Orders sheet, select
C2and type=VLOOKUP(B2,.
- Select the Products sheet tab at the bottom of the window, then select
A2:D6. Excel writesProducts!A2:D6into the formula.
- Press F4 so the range becomes
Products!$A$2:$D$6.
- Type
,3,FALSE)and press Enter.C2now holds=VLOOKUP(B2,Products!$A$2:$D$6,3,FALSE)and shows 39 for SO-5001.
- Drag the fill handle (the small square at the bottom-right corner of
C2) down toC4.B2becomesB3andB4, but the locked range stays put, so you get 12.99 and 59.99.
- For stock, enter
=VLOOKUP(B2,Products!$A$2:$D$6,4,FALSE)inD2and fill down. The results are 22, 140 and 18.
Sheet names with spaces. If the sheet is called Price List, the name needs single quotes: =VLOOKUP(B2,'Price List'!$A$2:$D$6,3,FALSE). Names with spaces or other non-alphabetical characters need the quotes, and Excel adds them when you click the range.
Whole columns. A range such as Products!A:D picks up new rows automatically. Keep the lookup value a single cell (B2, not B:B): Microsoft notes that a whole-column lookup value can cause a #SPILL! error, which a single cell or the @ operator fixes.
Excel tables. The table_array argument can also be a named range or an Excel table. Convert the product list to a table, name it tblProducts in the Table Name box on the Table Design tab (our guide to naming a table has the steps), and the formula becomes =VLOOKUP(B2,tblProducts,3,FALSE). The table name covers the data rows without the header, and the reference adjusts as you add or remove rows.
VLOOKUP between two workbooks
With both workbooks open, start the formula, switch to the source file, select the range and finish typing. Excel writes a workbook link with absolute references, such as:
=VLOOKUP(B2,[Prices.xlsx]Products!$A$2:$D$6,3,FALSE)
When you close Prices.xlsx, Excel adds the full path, for example 'C:\Reports\[Prices.xlsx]Products'!$A$2:$D$6. In Excel for Microsoft 365 on Windows, refresh, repoint or break these links from Data > Queries and Connections > Workbook Links; this pane replaced the older Edit Links command. Excel treats workbook links as potentially unsafe, so you may need to select Enable Content when you open the file. In Excel for the web, both workbooks must be saved in an online location your Microsoft 365 account can reach.
How to fix #N/A and other VLOOKUP errors
#N/A means VLOOKUP could not find the value. Microsoft's VLOOKUP, #N/A, #VALUE! and #NAME? pages list these causes and fixes.
| Error | Likely cause | Fix |
|---|---|---|
| #N/A | The value really is not in the first column | Check spelling and that the item exists; handle genuine misses with IFNA |
| #N/A | A number stored as text in one place and as a number in the other | Make both the same type: Ctrl+1 (Format Cells, Number), or Data > Text to Columns > Finish |
| #N/A | Leading or trailing spaces | Remove them with TRIM |
| #N/A or a wrong value | range_lookup is TRUE or blank and the first column is not sorted | Use FALSE for an exact match |
| #N/A after filling down | The range was not locked, so it slid down the sheet | Use $A$2:$D$6 or a table name |
| #N/A | The lookup value is not in the first column of table_array | Start the range at the lookup column, or use XLOOKUP |
| #REF! | col_index_num is larger than the number of columns in the range | Recount the columns in table_array |
| #VALUE! | col_index_num is text or less than 1 | Use a number of 1 or more |
| #VALUE! | The lookup value is longer than 255 characters | Shorten it, or use INDEX and MATCH |
| #NAME? | Text typed without double quotation marks, or a misspelled function name | Add the quotation marks; check the spelling |
| #SPILL! | A whole column such as A:A used as the lookup value | Use a single cell (A2) or the @ operator (@A:A) |
Once the data is clean, replace genuine misses with a message:
=IFNA(VLOOKUP("P-109",$A$2:$D$6,3,FALSE),"Not found")
This returns Not found because P-109 is not in the list. IFNA arrived in Excel 2013 and catches only #N/A. IFERROR also catches #REF!, #NAME? and other errors, which can hide a broken formula, and Microsoft's #NAME? guidance says to correct the syntax rather than mask that error.
Note that a stock of 0 for P-104 is a correct result. Microsoft also advises against storing numbers or dates in the first column as text, which can make VLOOKUP return unexpected values.
VLOOKUP limits, and when to switch
- It only looks right. Microsoft notes that VLOOKUP can only look up from left to right. For a column on the left, use INDEX with MATCH, or XLOOKUP.
- The column number is fixed. Insert a column inside the range and col_index_num 3 may now point at different data.
- The default is approximate. Leaving out FALSE is a common cause of wrong answers.
- It searches columns, not rows. HLOOKUP takes the same arguments but searches across rows, so use HLOOKUP for tables with headings across the top.
The XLOOKUP version of the cross-sheet formula is:
=XLOOKUP(B2,Products!$A$2:$A$6,Products!$C$2:$C$6,"Not found")
XLOOKUP defaults to an exact match and can return a column on either side of the lookup column, but Microsoft says it is not available in Excel 2016 or 2019. Our XLOOKUP guide covers its match and search modes.
For IT teams and businesses
VLOOKUP sits inside many business-critical spreadsheets. A few habits reduce the support load:
- Watch workbook links. A lookup that points at another file breaks when that file is renamed or moved. The Workbook Links pane lists every source and offers Change source to repair a broken link. Where possible, keep the reference list in the same workbook.
- Plan for mixed versions. XLOOKUP is not available in Excel 2016 or 2019, so templates shared with those users should stay on VLOOKUP or INDEX with MATCH. Both versions reached end of support on 14 October 2025, and Excel 2021 followed on 13 October 2026.
- Stop bad inputs at the source. A drop-down list on the lookup column (SKU, employee ID) prevents many typos that cause #N/A.
- Know when a spreadsheet has outgrown itself. Thousands of lookups across monthly exports become slow and fragile; a database, a Power Query merge or a reporting tool is usually a better home.
How eCorpIT can help
eCorpIT is a Gurugram-based technology organisation, founded in 2021, assessed at CMMI Level 5 and MSME certified, with senior-led engineering teams working across AWS, Microsoft and Google platforms. Our data and analytics services team helps organisations move lookup-heavy spreadsheets into dependable reporting, data models and Microsoft 365 workflows. Talk to us at /contact-us/.
Last updated: 15 November 2026.
