Using a VLOOKUP Formula in Excel

A VLOOKUP formula can be used to bring information from one spreadsheet to another as long as you have a similar field you can look up the information on. For example, in the example below I have Sheet1 which contains both product names and quantities. I also have Sheet2 that only contains product names and does not have the quantities. Since both sheets share the same products I can use a VLOOKUP formula to move the quantities from Sheet1 to Sheet2 looking up by the product name. Follow these steps:

  1. In Sheet2 (the sheet you would like to move data into), place your cursor in the field you would like to bring the data into.

  1. Go to Formulas>Lookup & Reference>VLOOKUP.

  1. You will receive the following window.

Lookup_value: This is what you are looking up on. In this case it will be cell A2 (Test Product 1) because this is what we are looking up in Sheet 1 to try and bring the quantity over associated with it.

Table_array: This is going to be the 2 columns over in Sheet1 we are looking up (Product name and Quantity).  

Col_index_num: This is where you select which column you would like returned between the ones you selected. In this case it will be 2.

Range_lookup: This will always be FALSE

  1. For the Lookup_value, select cell A2 so that it will lookup Test Product 1 in the other sheet.

  1. For the Table_array, switch over to Sheet1 and highlight both your cells. After highlighting the cells it will take those cells and place them in the Table_array back in Sheet2. Just place your cursor in Col_index_num then and it will return you to Sheet2.

  1. For the Col_index_num, just enter a 2. This is so that it returns column 2 of the columns you selected.

  1. For the Range_lookup, just enter FALSE. You will always enter FALSE here. This just tells it to look for an exact match.

  1. Click OK.

  1. You should see that it will bring over the quantity for this particular item that it found for it in the other spreadsheet.

  1. If you double click on the little black box in the bottom right hand corner of cell B2, it will drag that same VLOOKUP formula through all of the cells below it.

  1. Select all of column B and right click and click Copy.

  1. Right click on column B again and click Paste Special Values (123 icon).

  1. You have now successfully moved your data from one spreadsheet to another.
Have more questions? Submit a request


Article is closed for comments.
Powered by Zendesk