Do you want to become a vlookup expert?
Do you want to save time looking up data at work?
If you answered yes to either of these questions, then read on.
The vlookup is one of the most useful, yet most mis-understood functions in Excel.
This tutorial is a 7 step guide where we provide you with a file that you can use to learn how to vlookup between two different sheets in Excel – this works in all versions – Excel 2019, Excel 2016, Excel 2013, Office 365, etc.
Here is the link to the file we use – open this before starting the tutorial: vlookup between two different sheets – file.xlsx
Now that you have the file open, we’ll explain what’s in it, so you understand the context:
There are TWO worksheets in the file. Both contain a list of products which the majority of people would buy in a supermarket – Milk, Bread, Jam, etc.
However, only one sheet contains a list of prices which we’ll use the vlookup to get into the other sheet.
We’ll use the vlookup function to get the prices of the food data from the “worksheet with prices” tab into the “worksheet without prices” tab.
Before you start, open the file from this site AND make sure you’ve clicked the “Enable editing” button (see screenshot below) then save it to your Desktop, otherwise any changes you make won’t work!
When you open the file (“vlookup between two different sheets”) you will see the screen below. Column C in the “worksheet without prices” tab is where we will pull in the “Price of the Goods” from the “worksheet with prices” sheet. Cell C2 in the “worksheet without prices” tab should already be selected, as that’s where we want the first result to appear.
Click on the “fx” button which is above column D (see screenshot below). The ‘Insert Function’ window will then show up (also shown in the screenshot below). If the “vlookup function” is already selected, like in the screenshot below, click “ok”. If it’s not, the text below this screenshow will show you what to do next.
If the “vlookup function” isn’t already selected, then in the “or select a category” field shown in the screenshot above, change the option in the drop-down from “Most Recently Used” to “All” then scroll down until you get to the vlookup function, as shown in the screenshot below.
Once you’ve selected the “vlookup function” from the drop-down menu and clicked ok, the “Function Arguments” window will appear.
The “Function Arguments” window will show 4 different fields which need to be populated for the vlookup to work. For the first field, the “Lookup_value”, click on cell A2, as illustrated in the image below. It has green dotted lines around it in the screenshot below.
Now click into the “Table_array” field, which is the field below the ‘Lookup_value’ field (see the screenshot above). A “table array” is a fancy phrase for describing two or more columns of data in Excel.
We’ll populate the data for this field in Step 5.
This part is where the other worksheet comes in! After you’ve clicked in the “Table_array” field, click into the other worksheet (“worksheet with prices”) and highlight columns A – C in that worsheet.
We highlight columns A – C in that worksheet because column A has the “Lookup_value” that is in the “worksheet without prices” and column C has the “Prices” that we want to get.
When you highlighted columns A – C, you may have noticed that the characters “3C” appeared at the top of column E – this is a quick way of Excel telling you how many columns there are in the data you’ve highlighted – it saves you having to count the columns manually, which a lot of people in offices do! If you didn’t notice that, clear the data in the “Table array” field and highlight columns A – C again in the “worksheet with prices” tab but ensure you hold don’t release the mouse key, when you’re highlighting the columns, because as soon as you release it, the “3C” will disappear.
Many office workers don’t know this trick and waste a lot of time manually counting the columns! But we’re here to save you time!
Your screen should now look like this:
NB – an alternative, but IMPORTANT way to populate the “Table_array” field is to highlight the range of data you’re looking up, starting with your first unique value – in this case cell A2. So you’d highlight cells A2 to C11, because C11 is the last cell in the range. You’d then need to FIX this table array by pressing F4 on your keyboard or ‘fn and F4’ at the same time if you have an HP laptop. Pressing F4 automatically adds in dollar signs, to fix the range of data your analysing – once pressed, “Table_array” field should look like the screenshot below ie a dollar sign before the letter A, before the number 2, before the letter C and before the number 11:
This is useful to know if a spreadsheet doesn’t allow you to highlight columns A to C because some cells are merged or you’re getting an invalid error. But for now, just clear everything in the ‘Table_array’ field, then highlight columns A-C again in the ‘worksheet with prices’ tab. Your screen should now look the same as the screenshot below.
And you can now proceed to Step 6, the second last step.
Click into the “Col_index_num” field – you will notice that your screen returns back to the first worksheet, the “worksheet without prices” – don’t panic! All you have to do here is put in the number 3 in the “Col_index_num” field shown in the screenshot below.
We put in the number 3 here because column C in the “worksheet with prices” has the prices of the goods that we want and it is three columns away from column A, which has the unique lookup values that we are using. Or if you noticed the “3C”, it means “3 columns” so you know you have to insert the number 3 in the “Col_index_num” field. A screenshot of how everything should look so far is below.
NB if you get an error like the one in the screenshot below, it means that you’ve accidentally clicked on the ‘worksheet without prices’ tab twice or clicked on something which has resulted in additional text appearing in the ‘Table_array’ field – in the screenshot below, the text ‘worksheet with prices’! (highlighted in yellow) appears twice, which is what’s causing that error.
The solution here is to just clear everything in the ‘Table_array’ field then ensure that you’re still in the ‘worksheet with prices’ tab, then highlight cells columns A – C again.
To recap, your screen should look like this before proceeding to the last step (Step 7)
The final step! Click into the ‘range lookup field’ and type the word ‘FALSE.’
Now click ‘OK’ and, like magic, the formula will return the first result that we’re looking up i.e $1.44.
After that, simply drag down the formula in cell C2 in the “worksheet without prices” and the “prices” of all the other goods from the other sheet will auto-populate.
The word ‘FALSE’ in the ‘Range_lookup’ field ensures that the vlookup only returns an EXACT match. If it doesn’t find an exact match for the ‘lookup value’ we’re using, then it will return an N/A – more about this in the “13 reasons why your vlookup is not working” page mentioned at the end of this.
Your formula in the end should look like this (in cell C2 in the worksheet without prices):
=VLOOKUP(A2,’worksheet with prices’!A:C,3,FALSE)
If your formula does not look like this, then you need to check that you’ve followed the steps above correctly.
And that’s how you do a vlookup between two sheets!
IMPORTANT point to note – the numbers in the “lookup value” column (column A in the ‘worksheet with prices’ tab) MUST precede the data you’re looking up! So in the ‘worksheet with prices’ tab, if the numbers in column A were AFTER the prices in column C, then you wouldn’t be able to use a vlookup.
So with vlookups, you must ALWAYS move from the LEFT to the RIGHT – in this case, when we highlighted columns A – C in the ‘worksheet with prices’ tab, our ‘lookup_values’ started on the left (in column A) and the values we needed were to the RIGHT of column A ie column C.
If you want to learn how to do a vlookup between two workbooks, click here.
If you want to learn about Pivot Tables, you can do so via this website we created following user demand: https://pivottablesinexcel.com/
We ask that you take a moment to read our Terms and Conditions and Privacy Policies.