Have you ever had a large spreadsheet of data in Excel and need an easy way to filter and extract specific information from it? If you can learn how to use VLOOKUP in Excel, you can perform this search with just one powerful Excel function.
The VLOOKUP function in Excel is intimidating to many because it has many parameters and there are several ways to use it. In this article, you will learn about all the ways you can use VLOOKUP in Excel and why it is so powerful.
VLOOKUP Parameters in Excel
When you start typing = VLOOKUP (in any Excel cell, you will see a pop-up window with all the available function parameters.
Let’s take a look at each of these parameters and their meaning.
- lookup_value: the value you are looking for from the spreadsheet
- table_array: the range of cells in the sheet that you want to search by
- col_index_num: column from which you want to extract the result
- [range_lookup]: matching mode (TRUE = approximately, FALSE = exactly)
- Type = VLOOKUP (
- Select the School field, which is I2 in this example. Enter a comma.
- Select the entire range of cells containing the data you want to search. Enter a comma.
- Then, to get the reading score, you need to select the 3rd column from the leftmost selected column. So, enter 3 and then another comma.
- Finally, enter FALSE for an exact match and close the function with a).
- lookup_value: F2, final average score
- table_array: I2: J8, search range of literal scores
- index_column: 2, second column in lookup table
- [range_lookup]: TRUE, approximate match
These four parameters allow you to perform many different useful data searches within very large datasets.
Simple example of VLOOKUP Excel
VLOOKUP is not one of the core Excel functions that you may have already learned, so let’s start with a simple example.
In the following example, we will use the large SAT scores for US schools. This table contains information on over 450 schools as well as individual SAT scores in reading, math and writing. Feel free to download to follow. There is an outer join that fetches the data, so you’ll get a warning when you open the file, but it’s safe.
Searching such a large dataset of the school of your interest will take a very long time.
Instead, you can create a simple form in blank cells to the side of the table. To perform this search, simply make one school field and three additional fields for reading, math, and writing.
Then you will need to use the VLOOKUP function in Excel for these three fields to work. In the Reading field, create a VLOOKUP function as follows:
Once you select a range, you can start with the column you are looking for (in this case, the column with the school name), and then select all the other columns and rows that contain the data.
Note. VLOOKUP in Excel can only search cells to the right of the search column. In this example, the column with the name of the school should be to the left of the data you are looking for.
Your last VLOOKUP function should look something like this:
= VLOOKUP (I2, B2: G461,3, FALSE)
When you press Enter for the first time and complete the function, you will notice that the Reading field will be # N / A.
This is because the School field is empty and the VLOOKUP function cannot find anything. However, if you enter the name of any high school you want to search for, you will see the correct results from that row for your reading grade.
How to deal with VLOOKUP being case sensitive
You may notice that if you do not enter the school name in the same register as in the dataset, you will not see any results.
This is because the VLOOKUP function is case sensitive. This can be annoying, especially for a very large dataset where the column being searched is incompatible with capital letters.
To get around this, you can force to switch what you are looking for to lowercase before looking for results. To do this, create a new column next to the column you are looking for. Enter the function:
= TRIM (LOWER (B2))
This will shorten the school name and remove any extraneous characters (spaces) that might be to the left or right of the name.
While holding down the Shift key, move the mouse cursor to the lower-right corner of the first cell until it changes to two horizontal lines. Double click to automatically fill in the entire column.
Finally, since the VLOOKUP function will try to use the formula and not the text in these cells, you only need to convert them all to values. To do this, copy the entire column, right-click the first cell and paste only the values.
Now that all of your data is cleared in this new column, slightly modify the VLOOKUP function in Excel to use this new column instead of the previous one, starting the search range at C2 instead of B2.
= VLOOKUP (I2, C2: G461,3, FALSE)
Now you will notice that if you always enter your search in lowercase, you will always get good results.
This is a handy Excel tip to get rid of the fact that the VLOOKUP function is case sensitive.
VLOOKUP approximate match
While the exact match LOOKUP example described in the first section of this article is fairly straightforward, the approximate match is a little more complex.
An approximate match is best used to search across a range of numbers. To do this correctly, the search range must be sorted correctly. The best example of this is the VLOOKUP function to find a letter grade that matches a numeric grade.
If a teacher has a long list of students’ homework grades for a year with the last column average, it would be nice if the letter grade corresponding to that final grade appears automatically.
This is possible with the VLOOKUP function. All that is required is a lookup table on the right, which contains the corresponding letter grade for each range of numeric ratings.
Now, using VLOOKUP and approximate match, you can find the correct letter grade that matches the correct numeric range.
In this function, VLOOKUP:
After completing the VLOOKUP function in G2 and pressing Enter, you can fill in the rest of the cells using the same approach as described in the last section. You will see that all letter grades are filled in correctly.
Note that Excel’s VLOOKUP function searches from the bottom of the assigned letter grade range to the top of the next letter grade range.
Thus, “C” must be the letter assigned to the lower range (75) and the letter B to the lower (minimum) of its own letter range. VLOOKUP “finds†the result for 60 (D) as the closest approximate value for any number between 60 and 75.
VLOOKUP in Excel is a very powerful feature that has been around for a long time. It is also useful for finding matching values ??anywhere in an Excel workbook.
However, keep in mind that Microsoft users who have a monthly Office 365 subscription now have access to the new XLOOKUP feature. This function has more options and additional flexibility. Users with a 6-month subscription will have to wait until July 2020 for the update.
–