Buy Now
Add the VLOOKUP to your Excel toolbox as we go through the basics of how we can make the most out of this versatile Excel Function in this article

Looking Up on Using VLOOKUPs?

excel tips functions vlookup Nov 24, 2021

Anyone who has had any experience working with Excel has, one way or another, also encountered some sort of lookup function. Lookup functions are like Swiss Army knives for those who belong to the nine to five Excel grind.  

The VLOOKUP function in Excel is one of the Lookups and Reference functions that searches for a specific value in a row or column in Microsoft Excel and returns another value that’s in the same row across  a number of specified columns. Sounds like a mouthful? Buckle up as I take you on an in-depth guide on the VLOOKUP function.

VLOOKUP is just short for "Vertical Lookup," and it’s used to search for a row position of particular value in a specific column belonging to a table. When it finds that value, it returns the corresponding value of the chosen column in the same row. Thus, you can liken the work of this function to a phone book (If you’ve ever opened one), where you search for the number of a specific person by searching for their name first in the column of names, and when you find the desired name, you see the number that corresponds to it in the second column (the number column).

The General Structure of the VLOOKUP

VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])

Example of VLOOKUP to find a matching value

The following table contains the employee ID number and the employee’s name. We want to use the VLOOKUP function to find out the employee's name by searching for the ID number, the parameters of the VLOOKUP function will be as follows:

  • lookup_value: the value you want to search for, which is the value in cell E3
  • table_array: the table (the range) that contains the data, which is the range A2:B6. Note that the range does not include cell A1 and cell B1 addresses because we will not use them in the search. They are just addresses for data and not primary data.
  • col_index_num: the order of the column containing the value returned by the function, which is column number 2
  • [range_lookup]: Tells the function whether we’re looking for an approximate match, or an exact match. We want to find a matching value so that this parameter will take the value FALSE or 0.

In the following figure, the VLOOKUP function will search for the value “4” inside the first column in the range A2:B6, find it and return the corresponding value in the second column, which will give you the name “Shimaa”.

The VLOOKUP function will search for the value “1” inside the first column in the range A2:B6 in the following figure. It will find it and return the corresponding value in the second column, which is the name Ahmed.

.

VLOOKUP will search for the value “6” inside the first column in the range A2:B6 in the following figure. However, it will not find it. Therefore, it will not search for an approximate value and returns the error #N/A. The #N/A error appears when using the VLOOKUP function whenever it can’t find the lookup value. Whenever you do a VLOOKUP and you know you’re looking up an existing value, it might be an issue with how your data was inputted in the table. You could check out our tips and tricks video that talks about cleaning up messy data!

Example of VLOOKUP to Find an Approximate Value

The following is a guide on how to calculate the estimate. It shows what is the corresponding sum for each estimate as follows:

  • If the sum is greater than or equal to 0 and less than 50, the grade is Fail
  • If the sum is greater than or equal to 50 and less than 65, the grade is acceptable
  • If the sum is greater than or equal to 65 and less than 75, the grade is good
  • If the sum is greater than or equal to 75 and less than 85, the grade is excellent
  • If the sum is greater than or equal to 85 and less than or equal to 100, it is excellent

Accordingly, the parameters of the VLOOKUP function will be as follows:

  • lookup_value: the value you want to search for, which is the value in cell E3
  • table_array: the table (the range) that contains the data, which is the range A2:B6. Note that the range does not include cell A1 and cell B1 addresses because we will not use them in the search. They are just addresses for data and not primary data.
  • col_index_num: the order of the column containing the value returned by the function, which is column number 2
  • [range_lookup]: We want to find an approximate value so that this parameter will take the value TRUE or 1.

In the following figure, the VLOOKUP function will search for the value 65 in the first column in the range A2:B6. It will find it and return the corresponding value in the second column, the estimate Good.

The VLOOKUP function will search for the value 53 in the first column in the range A2:B6 in the following figure. It will not find it, so that it will search for the most considerable value smaller than the value 53. Then it will find the value 50 and return the corresponding value in the second column, which is the acceptable estimate.

If you’ve made it this far, I’m sure you can already imagine the plethora of possibilities in using VLOOKUP. Pair this with drop-down lists (which you can learn more about in this video) and you’ve got a really powerful tool for your reports and dashboards.

Lookup functions are the bread and butter of any dashboard. With the sheer amount of data that businesses no matter how big or small, having a quick and easy way to find values in a data table is a necessity. You can create employee databases with their ID numbers or order trackers using customer names or invoices numbers. You can see here in our Year Wise Analysis Template how the VLOOKUP function is combined with the IFERROR, and nested in an IF function as well.

The best part about this function is that it's one of the early versions of the lookup functions that Excel has to offer so it's super accessible, and you can even do it on Google Sheets. 

If you want to see some more great examples on how you could get creative with nested VLOOKUPS, most of Simple Sheets’ template dashboards uses them. Better yet, why not skip the hassle of making your own and sign up for FIVE FREE templates!

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras sed sapien quam. Sed dapibus est id enim facilisis, at posuere turpis adipiscing. Quisque sit amet dui dui.

Call To Action

Stay connected with news and updates!

Join our mailing list to receive the latest news and updates from our team.
Don't worry, your information will not be shared.

We hate SPAM. We will never sell your information, for any reason.