Replacing Legacy Lookups with Modern Solutions
Swipe to show menu
Throughout the course, several lookup functions were introduced:
VLOOKUP, HLOOKUP, XLOOKUP, INDEX with XMATCH.
All of these functions can retrieve data from tables, but they differ in flexibility, reliability, and modern relevance.
Older spreadsheets often rely on VLOOKUP or HLOOKUP. However, modern Excel workflows typically prefer XLOOKUP or combinations such as INDEX with XMATCH.
Comparing these approaches helps you understand when each method is appropriate.
The Same Problem Solved Three Ways
Assume the worksheet contains the following table.
Product Code | Product Name | Price |
|---|---|---|
P-101 | Mouse | 20 |
P-102 | Keyboard | 35 |
P-103 | Monitor | 180 |
P-104 | Webcam | 45 |
The product code is entered in cell E2.
The goal is to return the Price.
Approach 1 Using VLOOKUP
=VLOOKUP(E2; A2:C5; 3; FALSE)
VLOOKUP searches the first column of the table and returns the value from a specified column number.
Approach 2 Using XLOOKUP
=XLOOKUP(E2; A2:A5; C2:C5)
XLOOKUP searches the lookup column and returns the corresponding value from the return column.
Approach 3 Using INDEX and XMATCH
=INDEX(C2:C5; XMATCH(E2; A2:A5))
XMATCH finds the position of the lookup value. INDEX retrieves the corresponding value from the return column.
Comparison of Lookup Methods
Method | Strength | Limitation |
|---|---|---|
| Simple and widely used | Cannot look left, fragile column |
| Works for horizontal tables | Rarely used and limited |
| Modern and powerful | Requires newer Excel versions |
| Very flexible | Slightly more complex formulas |
Best Practice for Modern Excel
When creating new spreadsheets:
Use XLOOKUP for most lookup tasks.
When advanced flexibility is required, use INDEX with XMATCH.
Legacy functions such as VLOOKUP and HLOOKUP are mainly used when working with existing spreadsheets.
Thanks for your feedback!
Ask AI
Ask AI
Ask anything or try one of the suggested questions to begin our chat
