How to Do a VLOOKUP in Excel Between Two Sheets: A Step-by-Step Guide

How to Do a VLOOKUP in Excel Between Two Sheets

If you’ve ever had data scattered across multiple sheets in Excel, you might have felt like a detective piecing together clues. Luckily, VLOOKUP can save the day. This handy function allows you to search for a specific value in one sheet and find matching information in another. Let’s dive in!

How to Do a VLOOKUP in Excel Between Two Sheets

In this tutorial, you’ll learn how to use VLOOKUP to pull data from one sheet into another. Following these steps will help you efficiently find and match data across different sheets in your workbook.

Step 1: Open Both Sheets

Make sure both sheets are open in your Excel workbook.

Having both sheets open is crucial because you need to reference the data from both locations. Ensuring they are easily accessible will streamline the process.

Step 2: Identify Your Lookup Value

Decide what value you will use as the key to search for data.

The lookup value is the piece of information you want to find in another sheet. This could be something like a product ID or a name. Choose something unique to avoid errors.

Step 3: Go to the Sheet Where You Want the Data

Click on the sheet where you want to display the data from the second sheet.

Now you’re setting the stage to pull in the data. Make sure you’re in the correct sheet to avoid confusion later.

Step 4: Select the Cell for VLOOKUP

Click on the cell where you want your VLOOKUP result to appear.

Think of this cell as the destination for your search result. This is where the magic happens.

Step 5: Enter the VLOOKUP Formula

Type =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) into the cell.

This formula is the heart of VLOOKUP. "lookup_value" is your key, "table_array" is the range in the other sheet, "col_index_num" is the column number of the data you want, and "range_lookup" should be set to FALSE for an exact match.

Step 6: Reference the Other Sheet

For the table_array argument, click on the other sheet and select the range of cells to search in.

Referencing the other sheet correctly is vital. This ensures VLOOKUP knows where to look for your data.

Step 7: Complete the Formula

Finish filling in the rest of the formula and press Enter.

Double-check everything before you hit Enter. Once you do, the VLOOKUP will work its magic, and the result will pop up in the cell you selected.

After you complete these steps, the cell will display the data from the other sheet that matches your lookup value. Now you can enjoy the power of VLOOKUP for cross-sheet data retrieval.

Tips for How to Do a VLOOKUP in Excel Between Two Sheets

  1. Make sure your lookup value is unique to avoid incorrect matches.
  2. Always check that your table_array range is correct.
  3. Use absolute references (e.g., $A$2:$C$10) to lock your table_array range.
  4. Double-check your column index number; it should be within the table_array range.
  5. Use the FALSE argument in the range_lookup for exact matches only.

Frequently Asked Questions

What if my VLOOKUP returns a #N/A error?

It means the lookup value isn’t found in the table array. Double-check your data and range.

Can I use VLOOKUP with text values?

Absolutely! VLOOKUP works with numbers, text, or a combination of both.

What does the col_index_num represent?

It’s the column number in the table array from which to retrieve the data. For example, if you want data from the second column, enter 2.

How can I make sure my table_array range doesn’t change?

Use absolute references by adding dollar signs (e.g., $A$2:$C$10) in your range.

Can I use VLOOKUP for multiple sheets simultaneously?

Unfortunately, VLOOKUP works between two sheets at a time. For multiple sheets, consider using INDEX/MATCH or other functions.

Summary of Steps

  1. Open both sheets.
  2. Identify your lookup value.
  3. Go to the sheet where you want the data.
  4. Select the cell for VLOOKUP.
  5. Enter the VLOOKUP formula.
  6. Reference the other sheet.
  7. Complete the formula.


Learning how to do a VLOOKUP in Excel between two sheets can significantly boost your productivity. Instead of manually searching for and cross-referencing data, you can let Excel do the heavy lifting. Understanding and using VLOOKUP effectively can save you time and reduce errors.

Remember, the key to mastering VLOOKUP is practice. Play around with different datasets and scenarios to get comfortable with the function. And don’t stop here! Excel has many powerful functions waiting to be explored. Whether it’s learning about INDEX and MATCH or diving into pivot tables, there’s always more to discover.

So, fire up Excel and start experimenting. The more you use VLOOKUP, the more you’ll appreciate its capabilities. Happy Excel-ing!

Get Our Free Newsletter

How-to guides and tech deals

You may opt out at any time.
Read our Privacy Policy