Excel formula builder
VLOOKUP Formula Builder
Create a VLOOKUP formula for traditional left-to-right table lookups in Excel or Google Sheets. Use exact match for IDs, SKUs, names, and most business tables.
Builder inputs
The lookup column must be the first column in the table range. The column index counts from that first column.
=VLOOKUP(A2, Products!A:D, 4, FALSE)Use exact match for IDs, names, SKUs, and most lookup tables. Approximate match requires a sorted first column.How this formula works
- VLOOKUP searches the first column of the table range and returns a value from the selected column index.
- The final argument is FALSE for exact match and TRUE for approximate match.
- Because VLOOKUP only looks to the right, use XLOOKUP or INDEX MATCH when the return column is left of the lookup column.
Best fit
Best for
- Simple lookup tables where the lookup key is in the first column.
- Older Excel workbooks where VLOOKUP is already the team standard.
- Exact-match product, SKU, price, customer, and task reference tables.
Not for
- Left lookups where the return column is before the lookup column. Use XLOOKUP or INDEX MATCH.
- Returning every matching row. Use FILTER or QUERY when you need a list.
- Approximate match on unsorted data, because the result can be misleading.
Useful formula variations
=VLOOKUP(A2, Products!A:D, 4, FALSE)Use FALSE for IDs, SKUs, names, and unsorted lookup tables.
=IFERROR(VLOOKUP(A2, Products!A:D, 4, FALSE), "Not found")Wrap VLOOKUP in IFERROR when dashboards should not show raw #N/A errors.
=VLOOKUP(A2, Products!A:D, 4, FALSE)Replace Products with the tab that contains the lookup table.
=VLOOKUP(A2, RateTable!A:B, 2, TRUE)Approximate match is only safe when the first column is sorted from smallest to largest.
Sample data
| SKU | Item | Category | Price |
|---|---|---|---|
| A-100 | Keyboard | Hardware | 49 |
| A-101 | Mouse | Hardware | 25 |
| B-200 | Desk Mat | Office | 18 |
| B-201 | Notebook | Office | 7 |
Returned results from the sample data
| Lookup setup | Formula pattern | Returned result |
|---|---|---|
| A2 is A-100, table Products!A:D, column index 4 | Exact match VLOOKUP | 49 |
| A2 is B-201, table Products!A:D, column index 2 | Return Item | Notebook |
| A2 is B-200, table Products!A:D, column index 3 | Return Category | Office |
Column index guide
| Table range | Index 1 | Index 2 | Index 3 | Index 4 |
|---|---|---|---|---|
| Products!A:D | SKU | Item | Category | Price |
| Products!B:D | Item | Category | Price | Not available |
Troubleshooting
| Problem | Likely cause | Fix |
|---|---|---|
| #N/A result | The lookup value is not found in the first column of the table range. | Check the first column, spelling, hidden spaces, and whether the key is stored as text or number. |
| Wrong column returned | The column index is counted from the table range, not from the worksheet. | Count the selected table columns from left to right and update the index. |
| Approximate match gives a strange result | TRUE approximate match expects the first column to be sorted ascending. | Switch to exact match unless you are intentionally using a sorted tier table. |
Common mistakes
- Column index 1 returns the lookup column itself.
- Exact match uses FALSE, not TRUE.
- Approximate match can return misleading values when the first column is not sorted.
- VLOOKUP cannot return a column to the left of the lookup column.
Related formulas
FAQ
Does this formula work in Google Sheets?
Yes. Google Sheets supports VLOOKUP with the same lookup value, table range, column index, and match type arguments.
Should VLOOKUP use TRUE or FALSE?
Use FALSE for exact match in most business tables. Use TRUE only for sorted tier or breakpoint tables.
Why does VLOOKUP return #N/A?
The lookup value was not found in the first column of the table range, or the source contains hidden spaces or mixed text-number values.
Can VLOOKUP look left?
No. VLOOKUP returns columns to the right of the first table column. Use XLOOKUP or INDEX MATCH for left lookups.
Are these formulas generated with AI?
No. The builder uses built-in spreadsheet rules in your browser and does not call an AI service.
Do I need to upload my spreadsheet?
No. Enter ranges and criteria manually. The site does not upload, store, or process spreadsheet files.