VLOOKUP Basic Example in Excel and Google Sheets
Purpose
VLOOKUP is the lookup formula most people learn first, and for good reason — it solves the most common data…
VLOOKUP is the lookup formula most people learn first, and for good reason — it solves the most common data matching problem in spreadsheets. You have a list of codes, IDs, or names and you need to pull in corresponding information from another table. Employee ID to department. Product code to price. Customer number to contact. VLOOKUP handles all of these with a single formula. I have seen it used in finance departments, HR systems, operations teams, and school gradebooks. Despite newer alternatives, VLOOKUP remains relevant in every version of Excel and Google Sheets and appears in virtually every existing spreadsheet you will ever open. This guide walks you through a complete working example so you understand exactly how VLOOKUP thinks.
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 use FALSE for exact match. Behaviour is identical.
Arguments
| Argument | Required | Description |
|---|---|---|
| lookup_value | Required | The value you want to find. This is matched against the first column of your table. It can be a cell reference, a typed value, or a formula. Data types must match — a number will not match text containing that number. |
| table_array | Required | The range containing your data table. VLOOKUP always searches the first column of this range. Use absolute references with dollar signs so the range does not shift when you copy the formula down. |
| col_index_num | Required | Which column to return the result from, counting from the left edge of table_array starting at 1. If your table is A:D and you want column C, col_index_num is 3. |
| range_lookup | Optional | FALSE for exact match which is what you need for IDs and codes. TRUE for approximate match which requires sorted data and is used for range-based lookups. Defaults to TRUE if omitted — always specify FALSE explicitly to avoid surprises. |
How it works
VLOOKUP scans the first column of your table from top to bottom looking for a match to your lookup value. When it finds a match, it stops, moves across to the column number you specified, and returns whatever is there. With FALSE for exact match, if your lookup value does not exist in the first column, VLOOKUP returns #N/A rather than guessing. This is the correct and expected behaviour. With TRUE for approximate match, VLOOKUP finds the largest value in the sorted first column that is still less than or equal to your lookup value — useful for tax brackets and commission tiers. The key constraint that trips people up is that VLOOKUP only looks right. It searches the first column and returns from columns to the right. If you need to return something from a column to the left of your search column, VLOOKUP cannot help — you need INDEX MATCH or XLOOKUP. Another common mistake is forgetting to lock the table reference with dollar signs, causing the range to shift as you copy the formula down and breaking lookups in later rows.
Examples
1
Look up the price of each product by matching the product code against the price table.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product Code | Product Name | Category | Price |
| 2 | PRD-001 | Wireless Mouse | Peripherals | =VLOOKUP(A2,$F$2:$I$11,4,FALSE) |
| 3 | PRD-002 | USB Keyboard | Peripherals | 45.99 |
| 4 | PRD-003 | Monitor Stand | Accessories | 89.50 |
| 5 | PRD-004 | Laptop Bag | Accessories | 59.99 |
| 6 | PRD-005 | HDMI Cable | Cables | 12.99 |
| 7 | PRD-006 | Webcam HD | Peripherals | 79.00 |
| 8 | PRD-007 | Desk Lamp | Accessories | 34.50 |
| 9 | PRD-008 | USB Hub | Cables | 27.99 |
| 10 | PRD-009 | Mouse Pad XL | Accessories | 19.99 |
| 11 | PRD-010 | Headset Pro | Peripherals | 129.00 |
Row 2: 29.99
— PRD-001 was found in the first column of the price table and 29.99 was returned from column 4.
Column 4 of the range $F$2:$I$11 is the Price column — counting starts from column F which is col 1, G is col 2, H is col 3, I is col 4. The dollar signs lock the table reference so when you copy the formula to rows 3, 4, 5 and so on, the table stays fixed at F2:I11. Without the dollar signs, the table would shift and each row would look at a different part of your spreadsheet.
2
Return the department for each employee ID from an HR table.
| A | B | C | |
|---|---|---|---|
| 1 | Employee ID | Name | Department |
| 2 | EMP-005 | Eve Taylor | =VLOOKUP(A2,$D$2:$F$11,2,FALSE) |
| 3 | EMP-001 | Alice Chen | Engineering |
| 4 | EMP-002 | Bob Smith | Marketing |
| 5 | EMP-003 | Carol Jones | Finance |
| 6 | EMP-004 | David Kim | HR |
| 7 | EMP-005 | Eve Taylor | Sales |
| 8 | EMP-006 | Frank Brown | Engineering |
| 9 | EMP-007 | Grace Lee | Marketing |
| 10 | EMP-008 | Hank Patel | Finance |
| 11 | EMP-009 | Iris Wong | HR |
Row 2: Sales
— EMP-005 was found in the first column and Sales was returned from column 2.
VLOOKUP always searches the leftmost column of your table range. The return column is counted from there.
Common use cases
1. Match employee IDs from a timesheet to employee names and departments from HR records
=VLOOKUP(A2,HR_Table!$A:$D,3,FALSE)
2. Pull product prices from a price list when building sales invoices or order forms
=VLOOKUP(B2,PriceList!$A:$C,3,FALSE)
3. Look up a customer account number to retrieve their credit limit from the accounts table
=VLOOKUP(C2,Accounts!$A:$E,4,FALSE)
4. Find the tax rate for each country in a multi-currency sales report
=VLOOKUP(D2,TaxRates!$A:$B,2,FALSE)
5. Retrieve the manager name for each team member from an organisational chart table
=VLOOKUP(E2,OrgChart!$A:$C,3,FALSE)
Common errors
#N/A
The lookup value was not found in the first column of the table. The most common causes are the value genuinely not existing, a data type mismatch between a number and text version of the same number, or extra spaces in one of the values.
Fix: Use TRIM on both the lookup value and the table column to remove spaces. Check data types with ISNUMBER. Wrap the formula in IFERROR or IFNA to show a friendly message instead of the error.
#REF!
The col_index_num is larger than the number of columns in your table_array. For example requesting column 5 from a table that is only 3 columns wide.
Fix: Count the columns in your table_array and make sure col_index_num does not exceed that count. If your table is A:C then valid col_index_num values are 1, 2, and 3.
Wrong result — not an error
The fourth argument was omitted or set to TRUE, causing approximate matching on unsorted data. VLOOKUP returns the wrong row without showing any error.
Fix: Always explicitly use FALSE as the fourth argument for exact matching. Only use TRUE when your data is sorted ascending and you specifically need range-based approximate matching like commission tiers.
Tips and variations
Always lock your table with dollar signs
When you copy a VLOOKUP formula down a column, the table_array reference shifts by one row for every row you copy, breaking your lookups. Add dollar signs to lock it: use $A$2:$C$100 instead of A2:C100. You can press F4 after selecting the range to add dollar signs automatically in Excel.
=VLOOKUP(A2,$E$2:$G$100,3,FALSE)
Wrap in IFERROR for clean output
When a lookup value does not exist in the table, VLOOKUP returns #N/A. In professional reports this looks messy and can confuse readers. Wrap the entire formula in IFERROR to replace errors with a blank cell or a descriptive message. This is standard practice in any spreadsheet shared with stakeholders.
=IFERROR(VLOOKUP(A2,$E$2:$G$100,3,FALSE),"Not found")
Use a named range for the table
Instead of typing $E$2:$G$100 in every VLOOKUP, define the range as a named range called PriceTable. Then write VLOOKUP(A2,PriceTable,3,FALSE). Named ranges make formulas easier to read, automatically adjust when rows are added to the table, and eliminate errors from mistyped references.
=VLOOKUP(A2,PriceTable,3,FALSE)
Excel vs Google Sheets
Excel vs Google Sheets
VLOOKUP works the same in Excel and Google Sheets. The only difference is naming: Excel calls the fourth argument range_lookup and Google Sheets calls it is_sorted. Both use FALSE for exact match and TRUE for approximate match. Formulas copy between the two applications without any changes needed. One practical difference is that Google Sheets automatically expands column references like A:C to the full sheet column, while in Excel, using full column references can be slower on large datasets where a bounded range like $A$2:$C$1000 performs better.
Related reading
Frequently asked questions
The most common reason is a data type mismatch. If your lookup value is the number 1001 but the table column contains the text 1001 (stored as text not a number), VLOOKUP will not find it. Check by clicking a cell in the table column — if the value is left-aligned it is text; right-aligned means it is a number. Use VALUE() to convert text to numbers or TEXT() to convert numbers to text. Also check for extra spaces using TRIM on both sides.
No. VLOOKUP can only return values from columns to the right of the first column which is always the search column. If you need to return a value from a column to the left, use INDEX MATCH instead: =INDEX(A:A,MATCH(D2,C:C,0)) returns from column A by searching column C. XLOOKUP in Excel 365 and modern Google Sheets also handles this naturally without any special syntax.
FALSE forces an exact match — the lookup value must exist precisely in the first column or VLOOKUP returns #N/A. This is what you want for codes, IDs, names, and most business data. TRUE allows an approximate match where VLOOKUP finds the largest value less than or equal to your lookup value. This is useful for range lookups like tax brackets and commission rates, but requires the first column to be sorted ascending. If you omit the argument it defaults to TRUE which causes wrong results on unsorted data.
Yes, with one minor naming difference. Excel calls the fourth argument range_lookup; Google Sheets calls it is_sorted. Both use FALSE for exact match and TRUE for approximate match, and behaviour is identical. VLOOKUP formulas copy directly between Excel and Google Sheets without modification. The only practical difference is performance on very large datasets where bounded references perform better in Excel.
Use VLOOKUP when your lookup column is the leftmost column of your table and you just need to return values from columns to its right — it is simpler and perfectly adequate for this case. Switch to INDEX MATCH when you need to look left, when your column position might change and you want column name references instead of numbers, or when you need to match on multiple criteria. Switch to XLOOKUP if you have Excel 365 or modern Google Sheets — it is more flexible, handles errors natively, and does not require the lookup column to be first.