Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use LOOKUP Function Among Multiple Sheets in Excel

Microsoft Excel is a helpful program. Using Excel‘s tools and capabilities, you can conduct infinite operations on a dataset. Regularly, we must search for data on other sheets. Working on one sheet and navigating to other sheets for relevant data can be time-consuming and stressful. Therefore, it is necessary to work on many sheets in Excel simultaneously. The LOOKUP function is crucial for searching and querying across several sheets in several circumstances. This article discusses two appropriate examples of utilizing the LOOKUP function to perform operations across many sheets. Be bold and review these two practical examples of using the LOOKUP Function on Multiple Sheets in Excel.


Download Practice Workbook

If you would like a free copy of the illustrated workbook that we covered during the presentation, please click on the link that can be found just below this section.


Why Do We Use LOOKUP Function?

If you enter some information in a cell in a spreadsheet, the LOOKUP function will find it and retrieve the matching data from another cell. When applied to a collection, the LOOKUP function locates the supplied value within the initial column or row and then returns a deal stored at the exact location in the final column or row. The LOOKUP function can take dynamic and sorted values, and that’s its only flaw. The VLOOKUP function is analogous, but it restricts the user from doing the query typically inside the row and only delivers material together in a left-to-right fashion.


2 Ideal Examples to Use LOOKUP Function Within Multiple Sheets in Excel

To illustrate this point, let’s examine two representative datasets. The First Dataset consists of five columns titled Order ID, Product Name, Sales Rep, Delivery Date, and Status. Using the LOOKUP function, we will develop a formula to fetch a whole Order Record from another sheet titled OrderRecord when an Order ID is supplied. The Second Dataset contains four columns: Sales Rep, Salary, Salary Range, and Bonus. The columns Sales Rep and Salary comprise a table, and Similarly, the remaining two columns form a separate table. Using the VLOOKUP and LOOKUP functions, we will create two formulas to determine a sales representative’s Bonus and return the data to the SalaryBonus sheet. It is essential to note that both datasets belong to the Dataset sheet.

excel lookup function multiple sheets

Notes

I’ve also been using Microsoft Excel 365 to compose this post. You are free to select the version that best meets your needs, and whichever option you choose is acceptable to us.


1. Utilize LOOKUP Function Within Multiple Sheets to Find Order Records in Excel

The LOOKUP command enables the user to enter a value in one column or row and has it matched to some other field or column’s value. Throughout this context, we will build a system that fetches the whole record of a particular order from different sheets if we provide an Order ID. To complete the work with the assistance of the LOOKUP function, adhere to the guidelines below.

STEPS:

  • First, select the OrderRecord sheet as the Active sheet.
  • Second, create a search box named Order ID.
  • Third, choose four columns titled: Product Name, Sales Rep, Delivery Date, and Status in the B6:E7 range.

Creating the Data Model to Find Order Records in Excel

  • After that, pick the D4 cell and input the desired Order ID from the Dataset, in this case, R-0097.

Utilize LOOKUP Function Within Multiple Sheets to Find Order Records in Excel

  • Later, select the B7 cell.
  • Next, type the following equation in the Formula bar.

=LOOKUP($D$4,Dataset!$B$4:$B$10,Dataset!E4:E10)

  • Afterwards, hit the Enter or Tab key to see the outcome like the below one.

Utilize LOOKUP Function Within Multiple Sheets to Find Order Records in Excel

  • Now, we must use the same formula for other cells.
  • To attain this, drag the AutoFill Handle icon to cell E7.
  • Subsequently, we will see the intended result.

Utilize the AutoFill Handle icon to use the previous formula for other cells

  • Now, choose the D7 cell and go to the Home tab.
  • Later, select Date as the Number Format.
  • Consequently, we will get the desired output like the following.

Output of Utilizing LOOKUP Function Within Multiple Sheets to Find Order Records in Excel

Formula Breakdown

=LOOKUP($D$4,Dataset!$B$4:$B$10,Dataset!E4:E10)

For this formula to make sense, you need to know how to use the following Excel function:

LOOKUP Function

  • LOOKUP($D$4,Dataset!$B$4:$B$10,Dataset!E4:E10)

The LOOKUP function utilizes an item from one range, finds its equivalent in another, and returns it. We must put an Exclamation mark between the sheet’s name and the range to refer to data or cells in other sheets. In this example, by involving the LOOKUP function, we find Webcam.


2. Display Salary Bonus Employing LOOKUP and VLOOKUP Functions Throughout Different Sheets in Excel

Vertical Lookup is what the acronym VLOOKUP refers to. You can search over many columns using the built-in VLOOKUP function in Excel. This section will cover how we can overcome the limitation of the LOOKUP function using the VLOOKUP function and query over the data within multiple sheets. Here, we will find out the Bonus of Sales Reps according to their Current Salary utilizing the LOOKUP function. Follow the directions below to successfully finish the work with the help of the LOOKUP and VLOOKUP functions.

STEPS:

  • First, set the SalaryBonus sheet as the Active sheet.
  • Second, create a search box called Sales Rep.
  • Third, choose two columns, Current Salary, and Bonus, in the range B6:C7.

Building the Data Model to Display Salary Bonus

  • After that, select the C4 cell and input desired Sales Rep name.

Input the desired data in the search box

  • Later, pick the B7 cell and input the below formula in the Formula bar.

=VLOOKUP(SalaryBonus!C4,Dataset!B13:C18,2,FALSE)

  • Next, hit Enter to see the intended outcome.

Display Salary Bonus Employing LOOKUP and VLOOKUP Functions Throughout Different Sheets

  • Likewise, select the C7 cell and write the equation below in the Formula bar.

=LOOKUP(B7,Dataset!E13:E18,Dataset!F13:F18)

  • Lastly, hit the Enter or Tab key to get the intended result, like the following.

Display Salary Bonus Employing LOOKUP and VLOOKUP Functions Throughout Different Sheets

Formula Breakdown

=VLOOKUP(SalaryBonus!C4,Dataset!B13:C18,2,FALSE)

To understand this formula, you must be familiar with the following Excel function:

VLOOKUP Function

  • VLOOKUP(SalaryBonus!C4,Dataset!B13:C18,2,FALSE)

To search for a value in a table or range by its row number, we use the VLOOKUP function. To refer a data or cells in other sheets, we must use an Exclamation sign between the sheet name and range. In this demonstration, by involving the VLOOKUP function, we find 400.


Things to Keep in Mind

In the first example, the Order ID column is intentionally sorted. We did this because the lookup_vector must always be provided in ascending order when working with the LOOKUP function. Otherwise, the LOOKUP feature will malfunction. In contrast, we extract the Current Salary from a sales representative’s name in the second example. We could not sort the Current Salary column in this instance, and we must thus use the VLOOKUP function to obtain the value.

  • Sort the lookup_vector in ascending order.
  • If necessary, overcome the restriction of the LOOKUP function by using the VLOOKUP function.

Conclusion

If you follow the examples we just went through, you will be able to use the LOOKUP Function within Multiple Sheets in Excel going forward. On the ExcelDemy website, you can discover numerous articles that are comparable to this one. Keep using them, and let us know if you have any other ideas or ways to accomplish the task or if you’re looking for something different to try. If you have any questions or comments, please submit them in the area below.

Lutfor Rahman Shimanto

Lutfor Rahman Shimanto

Hi there! I am Lutfor Rahman Shimanto. I have completed my graduation in Information Technology from Jahangirnagar University. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of work, I enjoy Chess a lot. I am a founding member of the Jahangirnagar University Chess Club and an internationally rated chess player.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo