VLOOKUP Function in Excel and Google Sheets
Purpose
Search the first column of a table for a value and return data from another column in the same row
Return value
The value from the column specified by col_index_num in the matching row, or #N/A if not found
VLOOKUP was the first function that made me feel like I was doing something genuinely powerful in a spreadsheet. Before it, I copied values between sheets by hand, row by row. After it, I could join two tables of thousands of rows in seconds. The concept is simple: point VLOOKUP at a value to find, tell it which table to search, specify which column to return from, and it locates the matching row and pulls the data. Invoice price lists. Employee directories. Customer account tables. Product catalogues. Any time you have two lists connected by a shared identifier and you need to pull data from one into the other, VLOOKUP is the classic solution. Despite XLOOKUP being the better modern choice, VLOOKUP is still worth knowing because it is in almost every shared spreadsheet you will encounter — in finance teams, sales operations, and the work of Excel users across every industry.
Syntax
✓ Excel 2003+
✓ Google Sheets
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The fourth argument is named range_lookup in Excel and is_sorted in Google Sheets. Both work the same way — use FALSE or 0 for exact match in both applications.
Arguments
| Argument | Required | Description |
|---|---|---|
| lookup_value | Required | The value to search for in the first column of table_array. Can be a cell reference, typed value, or formula result. Data types must match — a number will not match text containing that number. |
| table_array | Required | The range containing your lookup table. VLOOKUP always searches the first column. Use absolute references with $ so the range does not shift when you copy the formula down. |
| col_index_num | Required | Which column to return, counting from the left edge of table_array starting at 1. Column 1 is the search column itself. Column 2 is the next one to the right. |
| range_lookup | Optional | FALSE or 0 for exact match — use this for IDs, codes, and names. TRUE or 1 for approximate match — requires sorted data. Defaults to TRUE if omitted so always specify FALSE explicitly. |
How it works
VLOOKUP scans the first column of table_array from the top row down, looking for a cell that matches lookup_value exactly (with range_lookup set to FALSE). When it finds a match, it stops at that row and moves across to the column specified by col_index_num, returning whatever value is there. If the value is not found it returns #N/A. The most important habit is locking the table range with absolute references. Writing VLOOKUP(A2,E2:G100,3,FALSE) and copying it down shifts the table reference by one row for every row copied. Add dollar signs: VLOOKUP(A2,$E$2:$G$100,3,FALSE). Press F4 after selecting the range in Excel to add them automatically. VLOOKUP has two key limitations: it searches only the first column of table_array, and it can only return values to the right of that column. For left-column lookups or formulas that must survive column insertions, use INDEX MATCH or XLOOKUP instead.
Examples
1
Look up the price of each product from a price list using the product code.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Lookup Code | Code | Product Name | Price |
| 2 | PRD-003 | PRD-001 | Wireless Mouse | 29.99 |
| 3 | PRD-002 | USB Keyboard | 45.99 | |
| 4 | PRD-003 | Monitor Stand | 89.50 | |
| 5 | PRD-004 | Laptop Bag | 59.99 | |
| 6 | PRD-005 | HDMI Cable | 12.99 | |
| 7 | PRD-006 | Webcam HD | 79.00 |
Row 2: 89.50
— PRD-003 was found in column D and 89.50 was returned from column F — column 3 of the range $D$2:$F$8.
Column 3 of $D$2:$F$8 is the Price column. Count always starts from the left edge of your table range, not from column A of the spreadsheet.
2
Return each employee's department from an HR table using their employee ID.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Lookup ID | Employee ID | Name | Department |
| 2 | EMP-004 | EMP-001 | Alice Chen | Engineering |
| 3 | EMP-999 | EMP-002 | Bob Smith | Marketing |
| 4 | EMP-003 | EMP-003 | Carol Jones | Sales |
| 5 | EMP-004 | David Kim | Finance | |
| 6 | EMP-005 | Eve Taylor | Engineering |
Row 2: Finance
— EMP-004 found in column D — Finance returned from column F. EMP-999 not found so IFERROR returns "Not found".
Always wrap VLOOKUP in IFERROR in shared reports. #N/A errors break charts, SUM formulas, and look unprofessional to stakeholders.
3
Use approximate match to look up a tax rate based on an income bracket.
| A | B | C | D | E | ||
|---|---|---|---|---|---|---|
| 1 | Name | Income | Tax Rate | Min Income | Rate | |
| 2 | Alice | 45000 | =VLOOKUP(B2,$E$2:$F$6,2,TRUE) | 0 | 10% | |
| 3 | Bob | 85000 | 25% | 30000 | 20% | |
| 4 | Carol | 125000 | 30% | 60000 | 25% | |
| 5 | David | 28000 | 10% | 100000 | 30% | |
| 6 | 150000 | 35% |
Row 2: 20%
— Income 45000 falls in the 30000-59999 bracket so VLOOKUP with TRUE returns the 20% rate.
Approximate match requires the first column of the table to be sorted in ascending order. VLOOKUP finds the largest value less than or equal to the lookup value and returns from that row.
Common use cases
1. Pull product prices from a price list sheet into a sales invoice based on product code
=VLOOKUP(B2,PriceList!$A:$C,3,FALSE)
2. Match employee IDs from a timesheet to full names and department names from an HR master table
=VLOOKUP(A2,HR!$A:$D,3,FALSE)
3. Find the applicable tax rate for each income level using a sorted bracket table with approximate match
=VLOOKUP(A2,TaxTable!$A:$B,2,TRUE)
4. Retrieve each customer credit limit from an accounts database using their account number
=IFERROR(VLOOKUP(C2,Accounts!$A:$E,4,FALSE),0)
5. Look up a grade description from a code reference table used in a reporting system
=VLOOKUP(A2,$F$2:$G$8,2,FALSE)
Common errors
#N/A — lookup value not found
Data type mismatch, extra spaces, or the value genuinely not in the first column of the table. Numbers vs text versions of the same value are the most common cause.
Fix: Use TRIM on the lookup value. Check data types with ISNUMBER. Wrap in IFERROR to show a friendly message: =IFERROR(VLOOKUP(...),"Not found").
#REF! — column number out of range
col_index_num exceeds the number of columns in table_array.
Fix: Count the columns in table_array from its left edge. Expand table_array if the column you need falls outside it.
Wrong result with no error
Fourth argument omitted or set to TRUE on unsorted data — VLOOKUP silently returns a wrong row.
Fix: Always explicitly write FALSE as the fourth argument for exact matching on IDs, codes, and names.
Tips and variations
Lock the table with dollar signs before copying down
Without absolute references, copying the formula down shifts the table by one row for every row. Press F4 after selecting the range in Excel to add dollar signs automatically.
=VLOOKUP(A2,$E$2:$G$100,3,FALSE)
Wrap in IFERROR for every shared report
#N/A errors in a shared file look broken to non-technical readers and break SUM and chart formulas. Always wrap with a friendly fallback message.
=IFERROR(VLOOKUP(A2,$E$2:$G$100,3,FALSE),"Not found")
Switch to XLOOKUP when compatibility with older Excel is not needed
XLOOKUP removes the column number, looks left, handles not-found natively, and defaults to exact match. Use it on Excel 365 or Google Sheets.
=XLOOKUP(A2,E:E,G:G,"Not found")
Excel vs Google Sheets
Excel vs Google Sheets
VLOOKUP works identically in Excel and Google Sheets with one naming difference — the fourth argument is called range_lookup in Excel and is_sorted in Google Sheets. Both accept TRUE/FALSE or 1/0 and behave the same. Formulas copy between the two without changes.
Related reading
Frequently asked questions
VLOOKUP searches the leftmost column of a table for a value, then returns the corresponding value from a column you specify to the right. The V stands for vertical, meaning it searches rows from top to bottom. It is used to pull matching data from one table into another using a shared key — a product code, employee ID, or any unique identifier that appears in both datasets.
The fourth argument controls whether VLOOKUP uses exact matching or approximate matching. FALSE means exact match — VLOOKUP only returns a result when it finds the lookup value exactly as written. TRUE means approximate match — VLOOKUP finds the largest value less than or equal to the lookup value, which requires the first column to be sorted ascending. For lookups using IDs, names, codes, or any text, always use FALSE. Only use TRUE deliberately for range-based lookups like tax brackets or commission tiers with sorted tables.
col_index_num counts columns starting from the leftmost column of table_array, not from column A of the spreadsheet. If your table starts in column D and you want to return from column F, col_index_num is 3 — D is 1, E is 2, F is 3. A common mistake is counting from column A which gives the wrong number when the table does not start at the beginning of the sheet.
XLOOKUP is the modern replacement for VLOOKUP and is superior in every measurable way. It can look in any column and return from any direction including to the left. It takes a direct reference to the return column instead of a counted column number, so inserting or deleting columns never breaks it. It defaults to exact match without requiring FALSE. It has a built-in argument for the not-found case so you do not need to wrap it in IFERROR. Use XLOOKUP on Excel 365, Excel 2021, or Google Sheets. Use VLOOKUP only when you need the file to open correctly in Excel 2019 or earlier.
#N/A means the lookup value was not found anywhere in the first column of table_array. The most common causes are: a data type mismatch where the lookup value is a number but the table column contains text representations of the same number (or vice versa), extra leading or trailing spaces in one of the values that make them look identical on screen but not match programmatically, the lookup value genuinely not existing in the table, or the range_lookup argument being omitted which defaults to TRUE and causes VLOOKUP to return #N/A or a wrong value on unsorted data.