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

Get FREE Advanced Excel Exercises with Solutions!

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
=VLOOKUP(B5,

VLOOKUP Function to Lookup Value From Another Sheet in Excel

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

VLOOKUP Function to Lookup Value From Another Sheet in Excel

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

VLOOKUP Function to Lookup Value From Another Sheet in Excel

  • 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-
=VLOOKUP(B5,Sales!$B$5:$D$11,3,0)
  • Finally, just hit the Enter

VLOOKUP Function to Lookup Value From Another Sheet in Excel

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-
=INDEX(

INDEX and MATCH Functions to Lookup Value from Another Sheet

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

INDEX and MATCH Functions to Lookup Value from Another Sheet

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

INDEX and MATCH Functions to Lookup Value from Another Sheet

  • Then type-
=INDEX(Sales!D5:D11,MATCH(

INDEX and MATCH Functions to Lookup Value from Another Sheet

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

INDEX and MATCH Functions to Lookup Value from Another Sheet

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

INDEX and MATCH Functions to Lookup Value from Another Sheet

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

INDEX and MATCH Functions to Lookup Value from Another Sheet

  • Lastly, write 0 for the exact match.
  • So the complete formula will be as follows-
=INDEX(Sales!D5:D11,MATCH('INDEX+MATCH'!C4,Sales!B5:B11,0))
  • 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.

VLOOKUP And INDIRECT Functions to Lookup Value From Another Sheet

  • Write the following formula in Cell C7
=VLOOKUP($C$4, INDIRECT("'"&B7&"'!$B$5:$D$11"),3,FALSE)
  • Later, just press the Enter button for the output.

VLOOKUP And INDIRECT Functions to Lookup Value From Another Sheet

  • 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

Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

4 Comments
  1. Nice but I gave up due to WAY TO MUCH advertising.

    • Hello, SS!

      Hope you are doing well. We are very sorry that you are facing some troubles with advertising. But will try our best to give you a nicer experience.

      Regards
      ExcelDemy

  2. Hi,

    I have used your formula but for some reason I am getting “N/A” on some of the cells. I can clearly see the sheet name.

    Can you please assist me on this? Thanks.

    • Reply Avatar photo
      Rubayed Razib Suprov Jan 29, 2023 at 10:57 AM

      Greetings TRISH,

      In your case, it is difficult to ans this problem without having a look at your worksheet . Please send the worksheet to our problem solving, hence we can assist you on this issue.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo