1. VLOOKUP – Vertical Lookup
VLOOKUP means
“vertical lookup.” It searches for a value in the first column of a dataset and
returns information from another column in the same row.
For example, imagine you have a product price list where Product IDs are in the first column, product names are in the second column, and prices are in the third column. If you want to know the price of Product ID P003, you can use this formula:
=VLOOKUP("P003",
A2:C5, 3, FALSE)
This formula tells Excel to search for P003 in the first column of the range A2 to C5. Once it finds P003, it looks at the third column of that same row and returns the price, which is 45. In real life, this is useful when you want to quickly check product prices in an inventory list.
2. HLOOKUP – Horizontal Lookup
HLOOKUP means
“horizontal lookup.” It works in a similar way to VLOOKUP but searches across
the first row instead of the first column.
For example, suppose you have quarterly sales data where the first row contains product names like Product A, Product B, and Product C, and the rows below contain Q1, Q2, Q3, and Q4 sales. If you want to know the Q2 sales for Product B, you can use this formula:
=HLOOKUP("Product
B", A1:D4, 3, FALSE)
This formula searches the first row for “Product B,” then goes down to the third row, which represents Q2 sales, and returns the value 1000. This is useful when your data is arranged horizontally, such as sales by product across different quarters.
3. LOOKUP – Approximate Lookup
The LOOKUP
function is one of the oldest lookup functions in Excel. It is mainly used when
you want to find an approximate match in a sorted list.
For example, imagine a grading scale where scores of 50 give grade D, 60 gives grade C, 70 gives grade B, and 80 gives grade A. If a student scored 75, you can use this formula:
=LOOKUP(75,
A2:A5, B2:B5)
This formula looks for 75 in the score column. Since 75 is not an exact match, it chooses the closest value that is less than or equal to 75, which is 70, and returns grade B. This is useful when assigning grades or levels based on ranges of values.
4. XLOOKUP – Modern Flexible Lookup
XLOOKUP is a
modern replacement for both VLOOKUP and HLOOKUP. It can search both vertically
and horizontally, and it is much more flexible.
For example, suppose you have a list of employees with their departments and regions. If you want to know the region where employee Ali works, you can use:
=XLOOKUP("Ali",
A2:A5, C2:C5)
This formula finds “Ali” in column A and returns the corresponding value from column C, which is East. This function is very powerful and is now recommended instead of older lookup functions.
5. INDEX + MATCH – Flexible
Combination
INDEX and MATCH
are two different functions, but when used together, they become a strong
alternative to VLOOKUP.
For example, if you have airline data with names, number of flights, and revenues, and you want to know the revenue of Airline C, you can use:
=INDEX(C2:C5,
MATCH("Airline C", A2:A5, 0))
Here, the MATCH function first finds the position of Airline C in the list (which is the third row). Then the INDEX function uses that position to return the value from the revenue column, which is 40000. This method is useful because it is more flexible than VLOOKUP and allows you to search in any column, not just the first one.
6. XMATCH – Position Finder
The XMATCH
function is like MATCH but more advanced. It tells you the position of a value
in a list.
For example, if you have a list of suppliers and you want to find the position of Supplier B, you can write: =XMATCH("Supplier B", A2:A5). This formula returns 2, which means Supplier B is in the second position. This is useful when you want to know where an item appears in a list.
7. FILTER – Conditional Lookup
The FILTER
function is a modern dynamic array function that allows you to return multiple
rows that meet certain conditions.
For example, if you have a list of orders with customer names, regions, and amounts, and you want to get all orders from the East region, you can write:
=FILTER(A2:D5,
C2:C5="East")
This formula returns all rows where the region column equals East. That means it will show Imran’s order and Zain’s order. This is very useful when you want to filter a dataset instantly without using Excel’s built-in filter buttons.
8. CHOOSECOLS – Extract Columns
The CHOOSECOLS
function helps you select specific columns from a dataset.
For example, if you have employee records with ID, Name, Department, and Salary, and you only want the Name and Salary columns, you can use:
=CHOOSECOLS(A2:D4,
2, 4)
This formula extracts only the second and fourth columns, which are Name and Salary. This is useful when you need only certain fields from a large dataset.
9. CHOOSEROWS – Extract Rows
The CHOOSEROWS
function works like CHOOSECOLS, but it selects rows instead of columns.
For example, if you have a list of cities with populations and you want to return the first and last rows, you can write:
=CHOOSEROWS(A2:B5,
1, -1)
This formula gives you the first row, which is Lahore, and the last row, which is Quetta. This is useful when you want to quickly pull out specific records from a dataset.
10. OFFSET – Relative Lookup
The OFFSET
function returns a cell value that is a certain number of rows and columns away
from a starting point.
For example, if you have sales data for Products A to D, and you start at cell A2 which contains Product A, then write:
=OFFSET(A2,2,1)
This formula moves two rows down from Product A (which lands on Product C) and one column to the right (which gives Sales of Product C), so it returns 1700. This is useful when you want to pick a value that is relative to another cell.
11. INDEX + XMATCH – Modern
Combination
Finally, INDEX
and XMATCH can also be used together for a modern and flexible lookup solution.
For example, if you have a list of items with their stock levels, and you want to find the stock of Charger, you can use:
=INDEX(B2:B5,
XMATCH("Charger", A2:A5))
Here, XMATCH finds the position of Charger in the list, which is row three, and INDEX uses that position to return the stock value, which is 50. This is a modern and more reliable way to look up values compared to older functions.
Summary
Lookup functions in Excel save huge amounts of time by instantly finding the information you need without manual searching. For example, VLOOKUP and HLOOKUP are useful when looking up prices or sales data, while XLOOKUP and INDEX with MATCH provide more flexible ways to pull details like employee regions or airline revenues. Functions like FILTER can extract all orders from a specific region, CHOOSECOLS and CHOOSEROWS can quickly pick out selected details, and OFFSET helps retrieve values relative to a starting point. XMATCH is handy for finding the position of an item, and INDEX with XMATCH gives a modern, reliable alternative to older lookup methods. Altogether, these functions make it easy to handle tasks such as checking stock levels, assigning grades, pulling salaries, or analyzing regional sales, turning complex data into quick answers.
0 Comments