How to Lookup Value from Another Sheet in Excel (3 Easy Methods)

While working in Excel looking up values is a common task for business purposes or educational or research purposes. For that, in some cases, we need to lookup the values from another sheet instead of the same sheet. It’s not such a difficult task. The methods described in this article will be quite helpful for you to lookup a value from another sheet in Excel.

To give a demo of the methods, I will use the following dataset which represents some salespersons’ sales in different regions.

1. Using the VLOOKUP Function to Lookup Value from Another Sheet in Excel

In our very first method, I’ll use the VLOOKUP function to lookup a value from another sheet. It’s the most used function to lookup values. The VLOOKUP function is used to look up a value in the leftmost column of a table and returns the corresponding value from a column to the right. Here we’ll lookup the sales for Jack and Bob.

Steps:

• Write the following formula in Cell C5–

• Then click on the sheet where your table array is located. My data is located in the sheet named ‘Sales’.

• Now select the array by using your mouse, and press the F4 key to lock the reference.

• Later, give the column number relative to the selected array from where you want to extract the value and then type 0 for the exact match.
• So the complete formula will be as follows-

• Finally, just hit the Enter

Now we have got the output for Jack.

• Then to find the output for Bob just drag down the Fill Handle.

Here’s the final output.

Read More: Different Types of Lookup to Apply in Excel

2. Combining INDEX and MATCH Functions to Lookup Value From Another Sheet

Now we’ll use the INDEX and MATCH functions to lookup value from another sheet. The INDEX and MATCH functions are very common alternates of the VLOOKUP function. The INDEX function is used to return a value or the reference to a value from within a table or range. The MATCH function is used to search for a specified item in a range of cells and then returns the relative position of that item in the range. Now let’s find the sales value for Jack using the combination.

Steps:

• In Cell C7 type-

• After that go to the sales sheet by clicking on the sheet title.

• Then select the range D5:D11 from where we’ll extract the output.

• Then type-

• Later, go back to your previous sheet by clicking on the sheet title.

• Then select the cell where our lookup value is located.

• Again go to the ‘Sales’ sheet and select the range (B5:B11) where our lookup value exists.

• Lastly, write 0 for the exact match.
• So the complete formula will be as follows-

• Finally, just press the Enter

Then you will get your expected output.

⏬ Formula Breakdown:

➥ MATCH(‘INDEX+MATCH’!C4,Sales!B5:B11,0)
The MATCH function will search for the value ‘Jack’ in the ‘Sales’ sheet between the range B5:B11 and it will return as-
3

➥ INDEX(Sales!D5:D11,MATCH(‘INDEX+MATCH’!C4,Sales!B5:B11,0))
Finally, the INDEX function will return the value from the range D5:D11 according to the output of the MATCH function and that is-
78923

Read More: How to Lookup with Multiple Criteria in Excel

3. Applying Excel VLOOKUP and INDIRECT Functions to Lookup Value From Another Sheet

This method is a bit different from the previous two methods. Here, we’ll apply the combination of INDIRECT and VLOOKUP functions to lookup a value from another two sheets and we’ll extract the output from both sheets simultaneously. The INDIRECT function in Excel is used to convert a text string into a valid cell reference.

Take a look that here I have made two datasets of sales for two consecutive months. Now we’ll find the sales for Jack in both sheets.

• Write the following formula in Cell C7–

• Later, just press the Enter button for the output.

• Then drag down the Fill Handle icon to get the output from the sheet ‘Feb’.

Now we have found the sales for Jack extracted from both sheets.

⏬ Formula Breakdown:

➥ INDIRECT(“‘”&B7&”‘!$B$5:$D$11”)
The INDIRECT function will return the reference B5:D11 to a range-
{“Sam”,”Canada”,44589;”Peter”,”USA”,72734;”Jack”,”Brazil”,78923;”Samuel”,”UK”,99320;”Willium”,”London”,84738;”Ron”,”Canada”,98210;”Bob”,”UK”,57832}

➥ VLOOKUP($C$4, INDIRECT(“‘”&B7&”‘!$B$5:$D$11”),3,FALSE)
Finally, the VLOOKUP function will return output from that range for the value of Cell C4 and that is-
78923

Read More: How to Lookup Text in Excel

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

Conclusion

I hope the procedures described above will be good enough to look up a value in another sheet in Excel. Feel free to ask any question in the comment section and please give me feedback.

Related Articles

• How to Lookup Multiple Values in Excel
• How to Lookup a Table in Excel
• How to Use VBA Lookup Function in Excel