How to Do a VLOOKUP

The VLOOKUP function in Excel allows you to find a piece of information in your spreadsheet, particularly if the information has been organized as a vertically organized table. Learning how to do a VLOOKUP will help you save time as the information you’re looking for will immediately come to you instead of you having to meticulously scroll and strain your eyes. You could look up the price of a product, or the salary of an employee based on their ID. Items can be found based on exact or approximate parameters. Once you learn the basics, VLOOKUP becomes a powerful tool for extracting data. 

This post will teach you how to do a VLOOKUP in Excel, but the function can also be used in Google Sheets and other spreadsheet applications, so it’s a great tool to know. 

The VLOOKUP Function

Before learning how to use the function, we need to know the different arguments, or parts, of the VLOOKUP syntax. 

The function looks like this: 

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Let’s break down what each of the parts means. 

lookup_value

This is the value you want to look up. There are many ways the lookup_value can be inputted into the function. It can reference another cell that has the determined value in it, in which case you would input the cell in question, such as A5, or click on the cell A5. The lookup_value could also be a specific word or phrase. In this case, it would have to be inputted with quotation marks such as “Apples” or “John Doe”. Finally, the lookup_value can also be a number, such as 0.7. 

Keep in mind that when you put in your look_up value that VLOOKUP will only search columns to the right of that cell. The lookup_value is usually located in the leftmost column of the table. 

table_array

The table_array is how you determine the range you want VLOOKUP to search for your desired information. Usually, it’s the data table you’re looking at. You can input the table_array in two main ways. You also have the ability to even lookup tables in different sheets or documents. 

The first way to input table_array is by cell. This way, you would input the upper leftmost cell in your table you want VLOOKUP to search. That would be followed by the bottom rightmost cell of the table. This range should include the column you inputted as your lookup_value and exclude the row of column names. You input the array in for the format of cells separated by a colon. For example, if your data begins in the cell A2 and ends the cell C15, then you would enter your table_array as A2:C15. 

If you want to copy your VLOOKUP formula and paste it into another cell, then it is recommended that you “lock” your range so the formula isn’t confused on where to look. To “lock” your cell range, you add the $ symbol in between the column letter and the row number. For example, “locking” A2:C15 would look like $A$2:$C$15. 

You could also input the range as the table name, if your table has one. Make sure to type the name of the table perfectly and keep it out of quotation marks. For example, if your table name is “2021SpringData”, then your table_array is 2021SpringData. 

To input table_array from tables in a different sheet, you would have to input the name of the sheet exactly followed by an ! symbol. For example, if your data spans A2 to C15 in the sheet titled “Profits” then you would input your table_array as Profits!$A$2:$C$15. To pull in information from a different Excel document, you would have to put the file name with Excel file extension in square brackets. For example, if you want to look at another file named “MyBusiness” for Profits sheet and the A2 to C15 table, your table_array will look like [MyBusiness.xlsx]Profits!$A$2:$C$15.  

col_index_num

This number indicates the column in the table that you want to extract information from. For example, if you have columns that run left to right reading “First name” “Last name,” and “Age”, and are looking for information in the “Age” column, you would type “3” as your col_index_num. Be careful to not type in the letter of the column of the spreadsheet or the syntax would be incorrect.

range_lookup

This value lets you determine if you want your VLOOKUP function to find an exact match or an approximate match. 

The exact match finds a value typed exactly as you did in your lookup_value, and is written in the function as either 0 or FALSE. An approximate match finds the closest thing to what you inputted as your lookup_value, and is written in the function as either 1 or TRUE. If you are looking for an approximate match, you will need to sort your lookup_value column in ascending order for accurate results.

Now that we have examined the individual parts, let’s look at an example to see how to do a VLOOKUP in practice. 

Using VLOOKUP in Excel Example 

Let’s use this data as our example. Remember that VLOOKUP searches from top to bottom. We will assume that we’re looking for someone’s salary, say the person with the ID 53. 

• The lookup_value will be 53 or a referenced cell with the number 53. 
• The table_array will be B3:E9. B3 marks the start of the dataset we are retrieving information from, and E9 marks the end. 
• The col_index_num will be 4. The information we are looking for, the salary, is in the fourth column of the table. Remember to always count from left to right. 
• The range_lookup will be FALSE because we are looking exactly for the person with the ID 53, and confident that there is a cell with that value in the lookup_value column. 

Now, it’s time to put all the pieces together. 

Our function is =VLOOKUP(H2, B3:E9, 4, FALSE)

Note that the lookup_value is now H2 because it is referencing the cell that has the ID number we want to look up, which is 53. If you didn’t want to use a cell reference, =VLOOKUP (53, B3:E9, 4, FALSE) would yield the same result. Hitting enter retrieves for us ID 53’s salary: $58,339. 

Let’s try taking a look at an approximate example. 

Let’s say we want to see what letter grade would correspond to the score 85. But because in this example we see that there is no 85 in the leftmost column (where our lookup_value will be), looking for an exact match will give us an error. Hence, this time, we will use TRUE, or an approximate match instead. 

The retrieved grade for a score of 85 is a B. Note that 85 is equally between 80 (a B grade) and 90 (an A grade). The VLOOKUP function retrieves the B score because 80 appears before 90 in the column. 

Now that you have learned how to do a VLOOKUP, feel free to try it with larger datasets! Being able to retrieve information from large tables will surely be a time saver. 

VLOOKUP Tips and Basic Troubleshooting

Here are some other things to keep in mind when learning how to do a VLOOKUP. 

• Remember that if you are looking for an approximate match that the lookup_value column is organized in an ascending order. Or else, you will get inaccurate, and often surprising, result retrievals. 
• If you are looking up a value that appears in more than one cell in your lookup column, VLOOKUP will only draw information from the first cell it finds. For example, if you have two cells in the column of your lookup_value that reads “John” with one in A2 and one in A6, then only information from the “John” in A2 would be looked up. 
• VLOOKUP is also case sensitive. If you type your look_up value as “apples” and you have an “Apples” in your data, then “Apples” would not be found. 
• Also, if you return with an #N/A error, it could mean that the match for the lookup_value doesn’t exist or another bug. If you used an exact match, try looking for an approximate match. You can also clean up your data of spaces, make sure everything is typed correctly, and double check to see if Excel is storing any numbers as text (which can be fixed by selecting a cell, clicking on the green button and have Excel mark it as a number).   
• Did you run into an #REF! error? Make sure you didn’t input a col_index_number greater than the number of columns you have.  

FAQ:

• What is a VLOOKUP?
• How is a VLOOKUP used?
• How do I format the VLOOKUP function? 
• When do I have to sort my leftmost or lookup column in ascending order? 
• How do I troubleshoot for VLOOKUP errors?