Learn Lookup Functions the Smart Way in Excel

Introduction

In this tutorial, you will learn how to quickly find, retrieve, and organize information in Excel tables using powerful lookup and reference techniques. These methods allow you to search for values, extract specific data, sort lists, remove duplicates, and transform tables into dynamic layouts. Each technique is explained with simple language, realistic scenarios, sample data tables, example formulas, and step-by-step formula breakdowns. By following this guide, you will be able to efficiently manage large datasets, create dynamic reports, and automate common data retrieval tasks without manually searching through rows or columns. Let started!

Table of Contents SECTION 1: Primary Lookup Functions. 1 1. VLOOKUP.. 1 2. HLOOKUP.. 2 3. XLOOKUP.. 3 4. LOOKUP.. 4 SECTION 2: Advanced Lookup & Reference Functions. 5 5. INDEX.. 5 6. MATCH.. 6 7. INDEX + MATCH.. 7 8. XMATCH.. 7 SECTION 3: Dynamic Array Lookup Functions. 9 9. FILTER.. 9 10. SORT.. 9 11. SORTBY.. 10 12. UNIQUE.. 11 13. CHOOSECOLS.. 12 14. CHOOSEROWS.. 13 15. TAKE.. 14 16. DROP.. 14 17. WRAPROWS.. 15 18. WRAPCOLS.. 16 19. TOCOL.. 17 20. TOROW... 17 Conclusion

 

 

SECTION 1: Primary Lookup Functions

1. VLOOKUP

VLOOKUP is a function used to find a value vertically in the first column of a table and return a corresponding value from another column in the same row. It is used when you have a vertical list of items such as student marks, employee salaries, or product prices, and you want to quickly retrieve information without scrolling through the entire list. For example, if a teacher has a table of student IDs and marks, VLOOKUP can find a student's marks by entering their ID. The lookup value must always be in the first column of the table, and it works well for lists where the search column is on the left.

Example Data Table:

Formula Example:
=VLOOKUP(103, A2:C6, 3, FALSE)

Formula Breakdown:

• 103 is the lookup value (Student ID).
• A2:C6 is the table range.
• 3 is the column number to return (Marks column).
• FALSE ensures an exact match.
  This formula searches for student ID 103 and returns Bilal’s marks, which is 92.

 

2. HLOOKUP

HLOOKUP works similarly to VLOOKUP but searches horizontally in the top row of a table instead of vertically. This is useful for tables where headers are arranged in the top row, such as monthly expenses or sales reports. For instance, if a company records expenses for different months in a horizontal table, HLOOKUP can retrieve the amount for a specific category and month by searching across the top row and returning the corresponding value in the data rows.

Example Data Table:

Formula Example:
=HLOOKUP("February", A1:D4, 4, FALSE)

Formula Breakdown:

• "February" is the lookup value.
• A1:D4 is the table range.
• 4 is the row number to return (Fuel row).
• FALSE ensures exact match.
  This formula searches for February and returns the third row value for Fuel, which is 5500.

 

3. XLOOKUP

XLOOKUP is the latest and most flexible lookup function that replaces both VLOOKUP and HLOOKUP. It can search values left, right, up, or down, and return a corresponding value from any column or row. XLOOKUP is very useful when your lookup column is not the first column or when you need to search in multiple directions. For example, in an employee salary table, you can search by name to return the salary, or by department to find an employee ID. XLOOKUP also has optional error handling to display a custom message if a value is not found.

Example Data Table:

Formula Example:
=XLOOKUP("Bilal", B2:B6, D2:D6, "Not Found")

Formula Breakdown:

• "Bilal" is the lookup value.
• B2:B6 is the lookup array (Names).
• D2:D6 is the return array (Salaries).
• "Not Found" is the message if no match exists.
  This formula finds Bilal in the Names column and returns his Salary, which is 75000.

 

4. LOOKUP

LOOKUP is an older function that searches for a value in a single row or column and returns a corresponding value from another row or column. This function requires the data to be sorted in ascending order for proper results. It is useful for simple tables such as product price lists or simple numerical lists. For instance, if a shop has a list of products and prices, LOOKUP can retrieve the price of a selected product quickly.

Example Data Table:

Formula Example:
=LOOKUP("Marker", A2:A6, B2:B6)

Formula Breakdown:

• "Marker" is the lookup value.
• A2:A6 is the search column.
• B2:B6 is the return column.
  This formula finds the Marker product and returns its price, which is 50.

 

SECTION 2: Advanced Lookup & Reference Functions

5. INDEX

The INDEX function returns a value from a specific row and column within a range. Unlike VLOOKUP, INDEX does not search for a value but extracts the value at a known position. It is very useful for retrieving data when you already know the row and column numbers or when used with MATCH to create dynamic lookups. For example, in a stationery inventory table, INDEX can be used to get the stock value of a specific item if you know the row and column numbers.

Example Data Table:

Formula Example:
=INDEX(C2:C6, 3)

Formula Breakdown:

• C2:C6 is the range containing Stock.
• 3 is the row number in the range.
  This formula returns the stock for Highlighter, which is 75.

 

6. MATCH

MATCH returns the relative position of a value within a row or column. It does not return the value itself but the position number, which is useful in combination with INDEX to create dynamic lookups. For example, in a city list, MATCH can find the position of Islamabad, which can then be used to extract additional data from a parallel table.

Example Data Table:

Formula Example:
=MATCH("Islamabad", B2:B6, 0)

Formula Breakdown:

• "Islamabad" is the lookup value.
• B2:B6 is the range of City names.
• 0 ensures an exact match.
  The formula returns 3, because Islamabad is the third city in the list.

 

7. INDEX + MATCH

Combining INDEX and MATCH is one of the most flexible lookup methods in Excel. MATCH finds the row or column position of a value, and INDEX retrieves the actual value from that position. This combination allows searching in any direction and avoids limitations of VLOOKUP. For example, in a product sales table, you can search for a product name using MATCH and then extract its sales using INDEX.

Example Data Table:

Formula Example:
=INDEX(C2:C6, MATCH("Monitor", B2:B6, 0))

Formula Breakdown:

• MATCH("Monitor", B2:B6, 0) returns the position of Monitor, which is 4.
• INDEX(C2:C6, 4) returns the Sales for Monitor, which is 78000.

 

8. XMATCH

XMATCH is the modern version of the MATCH function. It returns the relative position of a value within a row or column, but with more advanced options such as exact match, wildcard match, and reverse search. Unlike MATCH, XMATCH allows you to search from the end of a list backward and can handle dynamic arrays seamlessly. This is very useful in cases where you want to find the position of a value in a dataset that might contain repeated entries or when you want to locate the last occurrence of a value. For example, in an employee attendance table, XMATCH can quickly tell you which position a particular employee is in the list, and it can even search from bottom to top if you want the most recent entry.

Example Data Table:

Formula Example:
=XMATCH("Bilal", A2:A7, 0, -1)

Formula Breakdown:

• "Bilal" is the lookup value to find.
• A2:A7 is the lookup array (Employee names).
• 0 specifies an exact match.
• -1 searches from the bottom to top to find the last occurrence.
  The formula returns 6 because the last occurrence of Bilal in the list is in the 6th position.

XMATCH is extremely powerful when combined with INDEX or other dynamic array functions because it allows you to locate positions dynamically, search backward, and handle wildcards, making it ideal for modern Excel dashboards, reports, and analysis.

 

SECTION 3: Dynamic Array Lookup Functions

9. FILTER

The FILTER function in Excel is used to extract only the values that meet certain conditions from a table or range. Unlike VLOOKUP or INDEX, FILTER can return multiple matching results at once and automatically “spill” them into adjacent cells. This is very useful for reports, dashboards, or lists where you want to see only the relevant data. For example, in a sales table by region, FILTER can return all sales records where the region is “North,” showing multiple rows instead of just one value.

Example Data Table:

Formula Example:
=FILTER(B2:B6, A2:A6="North", "No Data")

Formula Breakdown:

• B2:B6 is the range of Sales values to return.
• A2:A6="North" is the condition to filter rows where Region is North.
• "No Data" is the message displayed if no match is found.
  The formula returns 520000 and 610000 for the North region automatically.

 

10. SORT

The SORT function organizes your data in ascending or descending order without changing the original table. This function is helpful when you want to rank students, sort sales, or organize inventory automatically. For example, a teacher can sort a list of student marks from highest to lowest to quickly identify top performers, and the sorted list will dynamically update if the data changes.

Example Data Table:

Formula Example:
=SORT(A2:B6, 2, -1)

Formula Breakdown:

• A2:B6 is the table to sort.
• 2 is the column index to sort by (Marks column).
• -1 indicates descending order.
  This formula sorts the students from highest to lowest marks: Bilal, Danish, Ahsan, Ali, Faizan.

 

11. SORTBY

SORTBY sorts a table or range based on values from another range. This is especially useful when sorting data by multiple criteria or sorting names based on sales or performance. For example, in a product sales table, SORTBY can sort product names according to their sales numbers instead of sorting alphabetically.

Example Data Table:

Formula Example:
=SORTBY(A2:A6, B2:B6, -1)

Formula Breakdown:

• A2:A6 is the list of products to sort.
• B2:B6 is the range to sort by (Sales column).
• -1 indicates descending order.
  The formula sorts products by sales: Laptop, Monitor, Printer, Keyboard, Mouse.

 

12. UNIQUE

UNIQUE returns only the distinct values from a list, removing duplicates automatically. This is helpful for summarizing data, creating dropdown lists, or generating reports where repeated values are not needed. For example, in a student attendance list, UNIQUE can show all the distinct students who attended class without repeating names.

Example Data Table:

Formula Example:
=UNIQUE(A2:A7)

Formula Breakdown:

• A2:A7 is the range to extract unique values from.
  The formula returns: Ali, Bilal, Danish, Fahad.

 

13. CHOOSECOLS

CHOOSECOLS picks specific columns from a table or range. This is useful when you want to extract only certain columns from a large dataset. For example, if an employee table has multiple columns but you only want to see Name and Salary, CHOOSECOLS can create a smaller table with only those two columns.

Example Data Table:

Formula Example:
=CHOOSECOLS(A2:D4, 2, 4)

Formula Breakdown:

• A2:D4 is the table.
• 2,4 selects columns 2 and 4 (Name and Salary).
  The formula returns:
  
  
  
   

14. CHOOSEROWS

CHOOSEROWS picks specific rows from a table. This is useful when you only want to extract certain rows for reporting or analysis. For example, in a product sales table, you can pick the top three products by row number to focus on them without changing the original table.

Example Data Table:

Formula Example:
=CHOOSEROWS(A2:B6, 1, 3)

Formula Breakdown:

• A2:B6 is the table.
• 1,3 selects the first and third rows.
  The formula returns:
  
  
   

15. TAKE

TAKE returns the first or last set of rows or columns from a table. It is helpful when you only need the top N or bottom N records from a dataset. For example, in a sales list, TAKE can display the first three rows of products or sales for a quick summary.

Example Data Table:

Formula Example:
=TAKE(A2:B6, 3)

Formula Breakdown:

• A2:B6 is the table.
• 3 takes the first three rows.
  The formula returns Laptop, Mouse, Keyboard rows.

 

16. DROP

DROP removes the first or last set of rows or columns from a table. This is useful when you want to ignore headers or remove unnecessary rows. For example, if a sales table has totals in the first row, DROP can remove it to show only product data.

Example Data Table:

Formula Example:
=DROP(A2:B5, 1)

Formula Breakdown:

• A2:B5 is the table.
• 1 removes the first row.
  The formula returns Laptop, Mouse, Keyboard rows.

 

17. WRAPROWS

WRAPROWS arranges a single column of data into multiple rows with a fixed number of items per row. This is helpful when formatting long lists to make them visually neat. For example, a list of students can be wrapped into rows of five students each for better display.

Example Data Table:

Formula Example:
=WRAPROWS(A2:A8, 3)

Formula Breakdown:

• A2:A8 is the list of Names.
• 3 wraps every three names into a row.
  The formula returns a table:
  
  
  
  
  

18. WRAPCOLS

WRAPCOLS is similar to WRAPROWS but arranges data into multiple columns instead of rows. For example, a list of products can be displayed in columns of four for easier viewing.

Example Data Table:

Formula Example:
=WRAPCOLS(A2:A6, 2)

Formula Breakdown:

• A2:A6 is the list.
• 2 wraps two items per column.
  The formula returns:
  
  
  
   

19. TOCOL

TOCOL converts a table or range into a single column. This is useful when consolidating data from multiple columns into one column. For example, a sales report with multiple months can be combined into a single column of sales figures for analysis.

Example Data Table:

Formula Example:
=TOCOL(A2:B4)

Formula Breakdown:

• A2:B4 is the table.
  The formula returns a single column: 5000, 4500, 4700, 5200, 4800, 4900.

 

20. TOROW

TOROW converts a table or range into a single row. This is useful when preparing data for charts or horizontal analysis. For example, monthly sales figures from a table can be converted into a single row for plotting a chart.

Example Data Table:

Formula Example:
=TOROW(A2:C3)

Formula Breakdown:

• A2:C3 is the table.
  The formula returns: 5000, 4500, 5200, 4800, 5300, 4700 in a single row.

Conclusion

Mastering these lookup and reference techniques in Excel will significantly improve your efficiency in handling data. You can easily find and extract specific values, rank or filter information, organize lists, and reshape data for analysis or reporting. By applying the examples and step by step explanations provided, you can build dynamic tables, generate reports, and make data-driven decisions with confidence. These skills are essential for anyone working with Excel, whether for business, education, or personal projects, and they provide a strong foundation for exploring more advanced data analysis features.