# Why doesn't VLOOKUP find rows added to the bottom of the table?

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).

**Excel:** `=XLOOKUP(G1,A:A,C:C)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| SKU | Product | Price | Stock |  | SKU | P-108 |
| P-101 | Desk lamp | 39.99 | 140 |  | Price |  |
| P-102 | Office chair | 249 | 32 |  |  |  |
| P-103 | Monitor arm | 89.5 | 75 |  |  |  |
| P-104 | Standing desk | 449 | 18 |  |  |  |
| P-105 | Keyboard | 74.95 | 210 |  |  |  |
| P-106 | Webcam | 59 | 96 |  |  |  |
| P-107 | Headset | 129 | 54 |  |  |  |
| P-108 | Laptop stand | 45 | 160 |  |  |  |

Formula in G2: `=XLOOKUP(G1,A:A,C:C)`

Result: $45.00

## 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.

## Other ways

- `=VLOOKUP(G1,A:D,3,FALSE)`: Whole columns work the same way in VLOOKUP.
- `=XLOOKUP(G1,A2:A1000,C2:C1000)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/vlookup-does-not-find-new-rows