Blog

How to Use VLOOKUP in Excel Between Two Sheets

This article explains how to use VLOOKUP in Excel between two sheets. VLOOKUP is a function that allows you to look up a value in one column and return a corresponding value from another column. It is useful when you want to retrieve information from a different sheet based on a common identifier. For example, you can use VLOOKUP to find the price of a product from a price list sheet based on the product name from an order sheet.

To use VLOOKUP in Excel between two sheets, you need to follow these steps:

  1. Prepare your data in two sheets with a common column. For example, if you have an order sheet with product names and quantities, and a price list sheet with product names and prices, make sure the product names are in the same column on both sheets.
  2. In the sheet where you want to display the results, enter the formula =VLOOKUP(lookup_value, Sheet_name!range, col_index_num, [range_lookup]), where:
    • lookup_value is the value that you want to look up. For example, if you want to look up the price of a product from the order sheet, select the cell that contains the product name.
    • Sheet_name!range is the range of cells on the other sheet that contains both the lookup value and the return value. For example, if you want to look up the price from the price list sheet, enter the sheet name followed by an exclamation point and then select the range of cells that contains the product names and prices.
    • col_index_num is the column number in the range that contains the return value. For example, if you want to return the price from the second column of the range, enter 2.
    • [range_lookup] is an optional argument that specifies whether you want an exact match or an approximate match. For example, if you want an exact match, enter FALSE or 0. If you want an approximate match, enter TRUE or 1.
  3. Press Enter to get the result. This will return the value from the other sheet that corresponds to the lookup value. For example, if you entered =VLOOKUP(A2,‘Price List’!$A$2:$B$6,2,FALSE) in cell B2 of the order sheet, you will get 15 as the result.
  4. Copy and paste or drag and fill the formula to other cells as needed. This will look up and return values for other products from the other sheet.

This is how you can use VLOOKUP in Excel between two sheets. VLOOKUP can help you to combine data from different sheets based on a common column. However, you should be careful about choosing the right arguments and handling errors or missing values.