Need to find a specific value in your spreadsheet fast? Lookup functions in Google Sheets can save you tons of time — whether you’re pulling prices, names, or any other data from a large table. But with so many options (like VLOOKUP, HLOOKUP, INDEX+MATCH, and XLOOKUP), it’s easy to get confused.
This friendly cheatsheet will help you understand the difference between these lookup formulas, when to use which one, and how to apply each with real examples. Whether you’re brand-new to spreadsheets or just tired of fixing broken VLOOKUPs, this guide is for you!
Let’s explore how these functions work and which one is best for your task in Google Sheets.
Meet the Lookup Family in Google Sheets
- VLOOKUP: Searches vertically (top to bottom)
- HLOOKUP: Searches horizontally (left to right)
- INDEX+MATCH: Searches with more flexibility in any direction
- XLOOKUP: The most advanced, flexible lookup (exclusive to Google Sheets, not in Excel legacy versions!)
Example Scenario: Finding Employee Salary from a Table
Let’s say you manage a small team and want to look up salaries based on employee names.
Employee Table
| Employee ID | Name | Department | Salary |
|---|---|---|---|
| 001 | Ali | Marketing | 50000 |
| 002 | Priya | Finance | 60000 |
| 003 | James | HR | 48000 |
| 004 | Nina | IT | 70000 |
You want to enter an employee’s name and automatically get their salary.
1. VLOOKUP
Formula:
=VLOOKUP("Priya", B2:D5, 3, FALSE)Step-by-Step
- Searches for “Priya” in the first column of range
B2:D5 - Returns the value from the 3rd column (Salary)
Limitations:
- Can’t look to the left
- Breaks if you change column order
2. HLOOKUP
Use Case:
Data is arranged horizontally instead of vertically.
Horizontal Table Example:
| Employee | Ali | Priya | James |
|---|---|---|---|
| Salary | 50000 | 60000 | 48000 |
Formula:
=HLOOKUP("Priya", B1:D2, 2, FALSE)Downside:
- Not ideal unless your data is truly horizontal
- Less commonly used
3. INDEX + MATCH
Formula:
=INDEX(D2:D5, MATCH("Priya", B2:B5, 0))Step-by-Step
MATCH("Priya", B2:B5, 0)finds her row number (2nd in list)INDEX(D2:D5, 2)gets salary from that row — 60000
Why It’s Better Than VLOOKUP:
- More flexible — can look in any direction
- Safer — won’t break if columns change
- Works great with dynamic or sorted data
4. XLOOKUP (Google Sheets Exclusive)
Formula:
=XLOOKUP("Priya", B2:B5, D2:D5, "Not Found")Why It’s Amazing:
- No need to count columns
- Works left, right, up, down
- Lets you set a default value if not found
- Cleaner and more intuitive for beginners
Advanced Use Example:
Find salary of name typed in cell G1, with fallback if name isn’t found:
=XLOOKUP(G1, B2:B5, D2:D5, "Employee not found")Pro Tips for Lookup Functions
- Always use
FALSEor0for exact match (unless approximate match is intended) - Use
IFERROR()to avoid errors in user-facing spreadsheets - Combine
XLOOKUPwithIMPORTRANGE()for cross-sheet lookups - Use
ARRAYFORMULA()withINDEX + MATCHto apply lookups to entire columns
Quick Reference Lookup Cheat Sheet
| Function | Best For | Direction | Flexible? | Sample Formula |
|---|---|---|---|---|
| VLOOKUP | Simple vertical lookups | Top to bottom | No | =VLOOKUP("Priya", B2:D5, 3, FALSE) |
| HLOOKUP | Horizontal tables | Left to right | No | =HLOOKUP("Priya", B1:D2, 2, FALSE) |
| INDEX+MATCH | Customizable lookups | Any | Yes | =INDEX(D2:D5, MATCH("Priya", B2:B5, 0)) |
| XLOOKUP | Modern, clean, error-free lookups | Any | Yes | =XLOOKUP("Priya", B2:B5, D2:D5) |
Choosing the right lookup function depends on your data layout and your goals. For most modern Google Sheets tasks, XLOOKUP or INDEX + MATCH are the most powerful and reliable. VLOOKUP and HLOOKUP are still useful in simple cases but can quickly become limiting as your sheets grow more complex.
With this cheatsheet, you’ll always know which tool to reach for — and how to use it like a pro. Happy spreadsheeting!