Why doesn't VLOOKUP find rows added to the bottom of the table?
Formula (Excel and Google Sheets)
=XLOOKUP(G1,A:A,C:C)The table range stops above the new rows. Use whole columns, or an Excel Table, so the range grows: =XLOOKUP(G1,A:A,C:C).
- Result on the example
- $45.00 in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
A formula written when the list ended at row 7, such as =VLOOKUP(G1,A2:D7,3,FALSE), never looks at rows 8 and 9, so newer products return #N/A. A:A and C:C cover every row, including ones added later, so P-108 is found.
=XLOOKUP(G1,A:A,C:C)Result45
G1"P-108"The value in G1.
A:A49 rows × 1 col: "SKU", "P-101", "P-102", "P-103", …A range: 49 cells in one column.
C:C49 rows × 1 col: "Price", $39.99, $249.00, $89.50, …A range: 49 cells in one column.
XLOOKUP(G1,A:A,C:C)45XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
G1"P-108" - lookup_array
A:A49 rows × 1 col: "SKU", "P-101", "P-102", "P-103", … - return_array
C:C49 rows × 1 col: "Price", $39.99, $249.00, $89.50, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(G1,A:A,C:C). We ran it in Google Sheets mode on the same data and it returns $45.00.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =VLOOKUP(G1,A:D,3,FALSE) | $45.00 | Whole columns work the same way in VLOOKUP. |
| =XLOOKUP(G1,A2:A1000,C2:C1000) | $45.00 | A generous fixed range also leaves room to grow, and keeps the header row out of the search. |
Common mistakes
- Whole columns pick up anything else typed further down. Keep other data out of those columns.
- An Excel Table (Insert, Table) is the tidiest fix: references like
Products[SKU]grow with every new row. - Rows inserted inside the range expand it automatically; rows added just below it don't.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why doesn't VLOOKUP find rows added to the bottom of the table
G2, return the price of the SKU in G1 with a lookup that also covers rows added below the table later.Questions
Are whole-column references slow?
Lookups stop at the last used row, so they are fine. Avoid whole columns inside array math such as SUMPRODUCT.
Does Google Sheets have the same problem?
Yes. Use open-ended ranges like A2:A there.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.