Vlookup With Multiple Criteria in Different Columns

Excel Lookup Functions – VLOOKUP, HLOOKUP, XLOOKUP, and INDEX-MATCH

Introduction

When your dataset grows big, finding the right information becomes challenging. Instead of scrolling through hundreds of rows, Excel provides Lookup Functions that act like a search engine inside your spreadsheet.

The most powerful ones are:

• VLOOKUP → Vertical Lookup
• HLOOKUP → Horizontal Lookup
• INDEX-MATCH → Combination for flexible lookups
• XLOOKUP → Modern, more powerful replacement

In this blog, we’ll explain these functions step by step with examples, comparisons, pros, and cons. By the end, you’ll know which function to use in which situation.

1. What is a Lookup Function?

A Lookup function in Excel means:
👉 “Search for a value in a dataset and return related information.”

Example:
You have a list of employee IDs and want to find the salary of a specific employee. Instead of manually searching, you can use VLOOKUP or XLOOKUP to get the answer instantly.

2. VLOOKUP Function

Definition

VLOOKUP searches for a value vertically (top to bottom) in the first column of a table and returns data from another column.

Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Arguments

1. lookup_value → The value you want to find.
2. table_array → The table where Excel will search.
3. col_index_num → The column number (from left to right) of the data you want.
4. range_lookup → TRUE (approximate) or FALSE (exact match).

Example

Dataset:

ID	Name	Department	Salary
101	Ravi	IT	40,000
102	Neha	HR	35,000
103	Amit	Sales	45,000

Formula:
=VLOOKUP(102, A2:D4, 4, FALSE)

👉 Result: 35,000 (salary of Neha).

Limitations of VLOOKUP

• Can only search from left to right.
• Breaks if the table structure changes.
• Slower on very large datasets.

3. HLOOKUP Function

Definition

HLOOKUP searches for a value horizontally (left to right) in the top row and returns data from another row.

Syntax

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Arguments

1. lookup_value → Value to search.
2. table_array → The table (top row must contain lookup values).
3. row_index_num → Row number from which to return result.
4. range_lookup → TRUE (approximate) or FALSE (exact).

Example

Dataset:

ID	101	102	103
Name	Ravi	Neha	Amit
Dept	IT	HR	Sales
Salary	40,000	35,000	45,000

Formula:
=HLOOKUP(103, B1:D4, 4, FALSE)

👉 Result: 45,000

When to Use

Rarely used.

• Only helpful when data is arranged horizontally.

4. INDEX-MATCH Combination

This is the most powerful lookup technique before XLOOKUP.

INDEX Function

Returns a value from a specific row and column.
Syntax:

=INDEX(array, row_num, [col_num])

MATCH Function

Finds the position of a value in a range.
Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

INDEX + MATCH

You can combine both:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example

Dataset:

ID	Name	Department	Salary
101	Ravi	IT	40,000
102	Neha	HR	35,000
103	Amit	Sales	45,000

Formula:
=INDEX(D2:D4, MATCH(102, A2:A4, 0))

👉 Result: 35,000

Why INDEX-MATCH is Better than VLOOKUP

✔ Can search both left and right.
✔ Doesn’t break if table structure changes.
✔ Works better with large datasets.

5. XLOOKUP Function (Excel 2019 & Office 365)

Definition

XLOOKUP is the modern replacement of VLOOKUP and HLOOKUP. It solves almost all limitations.

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Arguments

1. lookup_value → Value to search.
2. lookup_array → Range where Excel searches.
3. return_array → Range to return results from.
4. if_not_found → Optional, what to show if not found.
5. match_mode → Exact or approximate match.
6. search_mode → Search from first or last.

Example

Dataset:

ID	Name	Department	Salary
101	Ravi	IT	40,000
102	Neha	HR	35,000
103	Amit	Sales	45,000

Formula:
=XLOOKUP(103, A2:A4, D2:D4, “Not Found”)

👉 Result: 45,000

Advantages of XLOOKUP

✔ Works both vertically and horizontally.
✔ No need for column index numbers.
✔ Can return multiple values.
✔ Handles “not found” gracefully.

6. Comparing All Lookup Functions

Feature	VLOOKUP	HLOOKUP	INDEX-MATCH	XLOOKUP
Search Direction	Left → Right only	Top → Bottom only	Both ways	Both ways
Handles Table Change	❌ No	❌ No	✔ Yes	✔ Yes
Approximate Match	✔ Yes	✔ Yes	✔ Yes	✔ Yes
Return Multiple Values	❌ No	❌ No	❌ No	✔ Yes
Speed with Big Data	Medium	Medium	Fast	Fast
Availability	All Excel versions	All Excel versions	All Excel versions	Excel 2019+ / 365

7. Real-Life Use Cases

1. Employee Database – Find salary by ID.
2. Product Pricing – Get price of product code.
3. Student Marks – Find grade of roll number.
4. Inventory System – Match item code with stock quantity.
5. CRM Data – Lookup customer contact details.

8. Advanced Lookup Tricks

a) Lookup with Two Criteria

Use INDEX-MATCH with two conditions.

Example: Find Salary where Name = Neha AND Dept = HR.
Formula:
=INDEX(D2:D4, MATCH(1, (B2:B4=”Neha”)*(C2:C4=”HR”), 0))

b) Lookup Closest Match

Use MATCH with -1 or 1 for approximate.

c) Reverse Lookup (Search Last Match)

XLOOKUP allows search from bottom using [search_mode] = -1.

---

d) Return Multiple Columns with XLOOKUP

=XLOOKUP(102, A2:A4, B2:D4)

👉 Returns Neha, HR, 35,000 together.

9. Common Mistakes

• Forgetting FALSE in VLOOKUP (causes wrong results).
• Using wrong column index.
• Not locking ranges with $ in formulas.
• Searching for text with extra spaces → Use TRIM().

10. Best Practices

• Prefer XLOOKUP (if available).
• Use INDEX-MATCH for older versions.
• Always use exact match unless you really need approximate.
• Clean your data before applying lookup.
• Document formulas in comments for team use.

Conclusion

Lookup functions are like search tools inside Excel.

• VLOOKUP/HLOOKUP → Good for beginners but limited.
• INDEX-MATCH → More flexible and reliable.
• XLOOKUP → Modern, powerful, and future of Excel.

If you master these, you’ll save hours of searching work and make your reports smarter.

👉 Remember: In business, the faster you find information, the faster you make decisions. Lookup functions give you that speed.