Google Sheets VLOOKUP formula builder: look up a value in another table (VLOOKUP, XLOOKUP, INDEX MATCH)
Say where the list is, where the table is, and which column to bring back. Copy the formula. You can bring the answer for one cell or for a whole column at once, match a column to the left, get every match, or show your own text when nothing is found. It runs in your browser and nothing you type here is sent anywhere. Every formula below was run in a real Google Sheet on 3 November 2026 (upper and lower case, empty cells, repeated codes, a trailing space, symbols like * and ?, numbers and text numbers) once in a US-locale spreadsheet and once in a German-locale one with semicolons: all checks matched.
You're on the list. We'll email you once.
How to use it
Paste the formula into one empty cell. VLOOKUP looks for the value in the first column of your table and brings back the column you pick. If the code you look up sits in column B but the answer is in column A, use INDEX MATCH or XLOOKUP: VLOOKUP cannot look to the left. The whole-column choice and "every match" spill down, so the cells below the formula must be empty.
Which one to pick
| You pick | The formula uses |
|---|---|
| VLOOKUP | VLOOKUP(A2, Prices!A:C, 3, FALSE) (FALSE means exact match) |
| Whole column | ARRAYFORMULA(IF(A2:A="", "", VLOOKUP(...))) |
| INDEX MATCH | INDEX(Prices!C:C, MATCH(A2, Prices!A:A, 0)) |
| XLOOKUP | XLOOKUP(A2, Prices!A:A, Prices!C:C) |
| Every match | FILTER(Prices!C:C, Prices!A:A=A2) |
| Upper and lower case apart | INDEX(..., MATCH(TRUE, ARRAYFORMULA(EXACT(...)), 0)) |
| Closest match | VLOOKUP(A2, Bands!A:B, 2, TRUE) |
The catches (found by testing in a real Sheet)
- Upper and lower case are the same. With codes
A100(price 1.5) anda100(price 9) in the table, a lookup of either one returned 1.5, the first row. Use the case-sensitive choice to get 9 fora100. - Repeated codes: the first row wins. With
F600twice (8 and 9), VLOOKUP, INDEX MATCH and XLOOKUP all returned 8. The "every match" choice returned both. - A space at the end breaks the match. A table cell
E500(with a space) was not found byE500: #N/A. Wrap the value inTRIM()or clean the table. - Stars and question marks are wildcards in VLOOKUP and INDEX MATCH. Looking up
A1*returned the row forA100; looking up*returned the very first row of the table, the header (Price). A code that really contains*or?can match the wrong row. XLOOKUP does not do this:A1*gave #N/A. - A number and the same number as text are different. A table with a text
10(price 4) and a number 10 (price 5): the number 10 found 5, the text "10" found 4. If your lookup list is text and your table holds numbers, nothing matches until you convert one side. - An empty lookup cell finds nothing. We had a table row with an empty code and a price of 11; an empty lookup cell returned #N/A, not 11. The whole-column choice leaves blank rows blank.
- An empty answer cell comes back empty. The price cell of
C300was empty and the lookup showed an empty cell, not 0. - Closest match needs a sorted first column. With bands 0, 90, 50, 70 (not sorted) the value 95 returned the label for 70 instead of the one for 90, with no error. Sort the first column from small to large first.
- #N/A. When nothing matches, Sheets shows #N/A. Fill in the "if nothing is found" box and the builder wraps the formula in
IFERROR. That also hides other errors, so use it once the formula works. - Decimal comma. In a German-locale sheet the same formula needs semicolons between arguments; pick the semicolon separator. The price 1.5 then shows as 1,5.
No code: KeepSorted
The add-on sorts the original table in place without code: no second copy, so formatting, checkboxes and notes move with their rows and people keep editing the same table. Select the range, pick the sort columns, Save. Free: one area per tab, up to 2 sort columns, no trial clock. Pro ($5/month or $39/year): up to 5 sort columns, custom orders like High > Medium > Low, ticked checkbox rows to the bottom, stacked tables sorted separately, and sorting after form responses, other editors' changes and an hourly check.
KeepSorted is built and in testing. It is not in the Google Workspace Marketplace yet. Leave your email and we send one message when you can install it.
The generators and the formulas stay free either way.
More KeepSorted tools and guides
- CONCATENATE formula builder (join cells, columns, ranges)
- SPLIT formula builder (text to columns or rows)
- COUNTIF / SUMIF formula builder (count, add up, repeats)
- Running total and percent-of-total formula builder
- RANK formula builder (ties, rank within a group)
- FILTER formula builder (text, number, date conditions)
- Unique values formula maker (UNIQUE, counts, duplicates)
- Sort formula generator (SORT, SORTN, QUERY order by)
- Auto sort script generator (Apps Script that sorts the original)
- StaySorted alternative: an honest comparison
Sources
- Google Docs Editors Help: VLOOKUP (sort order for the closest match)
- Google Docs Editors function list (VLOOKUP, XLOOKUP, INDEX, MATCH, FILTER, EXACT, IFERROR, ARRAYFORMULA)