How to Lookup in Excel (12 Examples)

Get FREE Advanced Excel Exercises with Solutions!

While using Microsoft Excel, you may frequently need to lookup data for different scenarios and find the matched values. Microsoft Excel offers some dedicated functions to perform some simple lookups. However, you may find it difficult while performing some advanced lookups, such as a lookup for multiple criteria, returning multiple values for matched criteria, or searching data from the left.

In this article, we will gather most of the commonly asked scenarios to lookup data in Excel. In the image below, you can see an overview of how to lookup data for some specific purposes.

Overview to Lookup in Excel

Go through the 12 examples to lookup in Excel.


1. Using the LOOKUP Function to Lookup in Excel

1.1 Lookup a Value in the Last Non-blank Cell in a Column

If you have lots of data and are having difficulties finding the latest entry in a column, you may use the LOOKUP function. For example, we want to lookup values associated with the last entry in column B.

To lookup the value for the last entry in column B, follow the steps below:

  1. Select a cell.
  2. Type the following formula:
    =LOOKUP(2, 1/(B:B<>""), B:B)
    Here, column B is both the lookup vector and the result vector. You can also extract the value from columns C and D by changing the result vector reference to columns C and D.
    Lookup Value in Last Non-blank Cell in a Column

1.2 Lookup a Value in the Last Non-blank Cell in a Row

Similar to lookup the last entry in a column, you can also lookup the non-blank entry in a row.

To lookup the value for the last entry in row 4, follow the steps below:

  1. Select a cell.
  2. Type the following formula:
    =LOOKUP(2, 1/(4:4<>""), 4:4)
    Here, 4 is the row reference for which the formula will find the last non-blank entry in row 4.
    Lookup Value in Last Non-blank Cell in a Row

1.3 Extract the Value Associated with the Latest Entry in a Row

Along with finding the latest entries for row and column, the LOOKUP function allows us to find the associated value too.

For example, we have the entries for the present days of employees. Now, we want to find the last day of the present for an employee.

To get the associated value with the latest entry, follow the steps below:

  1. Select a cell.
  2. Type the following formula:
    =LOOKUP(2,1/(C6:G6="Present"), $C$5:$G$5)
    Here, C6:G6 is the lookup vector range, and ‘Present’ is the criteria for which we will lookup the associated data. C5:G5 is the range of the associated data. So, the formula will result in ‘Fri’ as the latest day of the present for the employee Kane Orwell.
    Extract the Value Associated with the Latest Entry in a Row

1.4 Lookup Value in a Range

With the help of the LOOKUP function, you can lookup values within a range. For example, we have some taxable percentages under specific taxable income ranges. We will find the taxable percentages for each employee by using the LOOKUP function.

To lookup values within the range, follow the steps below:

  1. Select a cell.
  2. Type the following formula:
    =LOOKUP(2,1/((D5>=$B$19:$B$21)*(D5<=$C$19:$C$21)),$D$19:$D$21)
    Here, B19:B21 is the criteria range for minimum taxable pay. C19:21 is the maximum criteria range for certain taxable percentages. D19:D21 is the taxable percentages within the ranges. So, the formula for cell G5 results in 20% for a salary of $43,000.
    Lookup Value in Range

You can check out more alternative ways to lookup and return value in a range by following the link.


1.5 Using Named Range to Lookup Value in Table

You can lookup values in a table using named ranges. To do that, you first need to create a named range with a table and use the named range in the formula of the LOOKUP function.

To lookup values using named ranges, follow the steps below:

  1. Create a named range with a table range.
    Here, G4:H7 is the range in which we applied a named range (Salary_Grade).
  2. Here, G4:H7 is the range in which we applied a named range (Salary_Grade). Here, a salary of $20,000–$29999 will be categorized as grade C, $30,000–$39999 as grade B, and above $40,000 as grade A.
  3. Type the following formula:
    =LOOKUP(D5,Salary_Grade)
    Here, for the value of $43,000, it will result in A, according to the provided named range. Anything, below $20,000 will result in #N/A error as the value is out of range.
    Using Named Range to Lookup Value in Table

You can check out more alternative ways to lookup a table in Excel by following the link.


2. Using the VLOOKUP Function to Lookup Vertically

2.1 Lookup Vertically in Columns

The VLOOKUP function is a dedicated function that offers to lookup values vertically across different columns.

To lookup vertically in columns, follow the steps below:

  1. Select a cell.
  2. Type the following formula:
    =VLOOKUP(G4,B5:D16,3,FALSE)
    Here, G4 is the lookup value, and B5:D16 is the lookup range. 3 is the column index number to extract the value from the third column (Salary).
    Using the VLOOKUP Function to Lookup Vertically in Columns

2.2 Lookup Value from Different Sheets

You can also use the VLOOKUP function to lookup value from different sheets. To do that, you just need to input the sheet name reference in the formula.

To lookup values from different sheets, follow the steps below:

  1. Select a cell.
  2. Type the following formula:
    =VLOOKUP(C4,'Sample Dataset'!B5:D12,3,FALSE)
    Here, ‘Sample Dataset’ is the name of the source sheet.
    Lookup Value from Different Sheets

Similarly, you can apply the VLOOKUP function to lookup from different workbooks as well. You have to input the workbook name as the lookup range, and the workbook should have been opened. Additionally, you can also lookup across multiple sheets and summarize data in one sheet by going through the provided article link.


3. Using the HLOOKUP Function to Lookup Horizontally in Rows

The HLOOKUP function allows you to lookup horizontally across rows.

To lookup horizontally across rows, follow the steps below:

  1. Select a cell.
  2. Type the following formula:
    =HLOOKUP(C8,C4:F6,3,FALSE)
    Here, C8 is the lookup value in row 4, and C4:F6 is the lookup range. 3 is the row index number. So, the formula returns the value from row 6.
    Lookup Horizontally in Rows

4. Combining INDEX and MATCH Functions to Lookup Value from Left in Excel

The VLOOKUP or HLOOKUP cannot lookup from the left. But, we can perform a left lookup by using the combination of the INDEX and MATCH functions.

To lookup values from the left, follow the steps below:

  1. Select a cell.
  2. Type the following formula:
    =INDEX($B$5:$B$16,MATCH($G$4,$C$5:$C$16,0))
    Here, the formula will return the employee name for the lookup value of the joining date placed on the right side column of the Employee Name column.
    Lookup Value from Left

If you are a Microsoft 365 user, you can use the XLOOKUP function instead of the INDEX MATCH combination. The XLOOKUP by default, can perform a left lookup in Excel.


5. Combining INDEX MATCH Functions to Lookup Value with Multiple Criteria in Excel

The combination of INDEX and MATCH functions offers to lookup value with multiple criteria. In the following example, we will find the employee’s name based on the joining date and a salary greater than $42,000.

To lookup values with multiple criteria, follow the steps below:

  1. Select a cell.
  2. Type the following formula:
    =INDEX(B5:B16,MATCH(1,(C5:C16=$G$4)*(D5:D16>G5),0),1)
    Here, cells G4 and G5 are the two criteria. So, the formula returns the matched value in cell G7 under the two criteria.
    Lookup Value with Multiple Criteria

You can check out more alternative ways to lookup value with multiple criteria in Excel by following the link.


6. Using the FILTER Function to Return Multiple Values for a Single Lookup Criteria

As the FILTER function is a kind of array function, you can return multiple values for a single criterion.

To return multiple values for a single criterion, follow the steps below:

  1. Select a cell.
  2. Type the following formula:
    =FILTER($B$5:$B$16,$C$5:$C$16=G$4)
    Here, the cell G4 contains the single criteria. So, the formula returns multiple matches from the return range B5:B16.
    Return Multiple Values for a Single Lookup Criteria

The FILTER function is only available for Microsoft 365 users. If you are not a 365 user, you can get to the link for an alternative way to return multiple values based on a single criteria.


7. Using the TEXTJOIN Function to Lookup and Return Multiple Values into One Cell

The TEXTJOIN function allows you to join text from different cells. So, we can return multiple values by applying this function combined with the IF and TRUE functions.

To lookup and return multiple unique values at once, follow the steps below:

  1. Select a cell.
  2. Type the following formula:
    =TEXTJOIN(",",TRUE,IF(D18=C5:C16,B5:B16,""))
    Here, the D18 cell contains the lookup criteria. So, the formula returns multiple matches in a single cell separated by a delimiter from the return range B5:B16
    Lookup and Return Multiple Unique Values into One Cell

The TEXTJOIN function is only available to Microsft 365 users. If you are not a 365 user, you may apply an alternative way to return multiple unique values concatenated into one cell by following the link.


Download Practice Workbook


Conclusion

This article will help you to easily lookup your data in Excel for different purposes. We have tried to gather all the possible queries for looking up data in Excel to make your task easier than before. Try to practice them all, and reach out to us for any difficulties or any other queries.


How to Lookup in Excel: Knowledge Hub


<< Go Back to Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo