Ever needed to quickly find a value in a big table of data in Google Sheets? That’s exactly what VLOOKUP and XLOOKUP are made for. These powerful functions help you search for information and pull out what you need — instantly. Whether you’re tracking prices, pulling customer info, or organizing a class list, lookup functions save time and reduce errors. In this guide, we’ll show you how to use both VLOOKUP and XLOOKUP with simple examples that anyone can follow — no spreadsheet wizardry required!
What Are VLOOKUP and XLOOKUP?
VLOOKUP stands for “Vertical Lookup.” It searches for a value in the first column of a table and returns a value from another column in the same row.
XLOOKUP is the more flexible, newer version of VLOOKUP. It can search both vertically and horizontally, works even if the lookup column isn’t first, and gives you more control over errors.
Real-Life Scenario: Looking Up Product Prices
Imagine you’re managing a product catalog and want to find the price of an item by typing its name. Instead of scrolling through rows manually, you can use VLOOKUP or XLOOKUP to find the price instantly.
Sample Table: Product List
| Product | SKU | Price |
|---|---|---|
| Keyboard | KB101 | 25 |
| Mouse | MS202 | 15 |
| Monitor | MN303 | 120 |
| Laptop Stand | LS404 | 35 |
Using VLOOKUP: Step-by-Step
Goal: Find the price of a product using its name
- Click the cell where you want the price to appear (e.g.,
D2). - Enter the formula:
=VLOOKUP(C2, A2:C5, 3, FALSE) - Press Enter.
What it does: This looks at the product name in C2, searches for it in the first column of A2:C5, and returns the price from the 3rd column.
Example:
| Product to Find | Price (Using VLOOKUP) |
|---|---|
| Mouse | 15 |
Important Tips:
- The lookup value (product name) must be in the first column of the range.
- The column number in the formula (e.g., 3) tells Sheets which column to return the result from.
- Use
FALSEto get an exact match.
Using XLOOKUP: Step-by-Step
XLOOKUP is even easier and more flexible.
- Click the cell where you want the result.
- Enter the formula:
=XLOOKUP(C2, A2:A5, C2:C5) - Press Enter.
What it does: This looks for the value in C2 in the range A2:A5 and returns the corresponding price from C2:C5.
Same Example Using XLOOKUP:
| Product to Find | Price (Using XLOOKUP) |
|---|---|
| Mouse | 15 |
Why XLOOKUP Is Better
- You don’t need to count columns — just match the lookup and return ranges.
- Works even if the lookup value is not in the first column.
- Automatically returns
#N/Aif not found — or you can customize it.
Common Use Cases for VLOOKUP/XLOOKUP
- Looking up product prices by name or SKU
- Finding a student’s grade by name or ID
- Matching employees to departments or salaries
- Pulling customer info from a sales sheet
Pro Tips
- Use
IFERROR()to display a custom message if the item is not found:
=IFERROR(XLOOKUP(C2, A2:A5, C2:C5), "Not found") - Keep your lookup and return ranges the same size in XLOOKUP to avoid errors.
- Make ranges dynamic using named ranges or array formulas if your data grows often.
VLOOKUP vs. XLOOKUP: Quick Comparison Table
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Can search left to right only | Yes | No |
| Flexible lookup and return ranges | No | Yes |
| Handles missing values | Limited | Yes (with custom result) |
| Available in Google Sheets | Yes | Yes (added in 2022+) |
Quick-Reference Cheat Sheet
| Use Case | Formula | Description |
|---|---|---|
| Find price using VLOOKUP | =VLOOKUP("Mouse", A2:C5, 3, FALSE) |
Finds “Mouse” in first column and returns price |
| Find price using XLOOKUP | =XLOOKUP("Mouse", A2:A5, C2:C5) |
Finds “Mouse” and returns price — more flexible |
| Handle not found values | =IFERROR(XLOOKUP("Tablet", A2:A5, C2:C5), "Not Found") |
Shows custom text if not found |
VLOOKUP and XLOOKUP are like your personal data detectives in Google Sheets. Whether you’re a student, small business owner, teacher, or just organizing your budget — these lookup formulas help you find answers faster and cleaner. Start with VLOOKUP if you’re just getting used to it, but don’t be afraid to jump into XLOOKUP for more advanced and flexible lookups. Either way, your spreadsheet skills are about to level up!