0800 3892304
info@spotlighttraining.co.uk

VLOOKUP Function

What does a VLOOKUP Function do?

A VLOOKUP Function is used to vertically look down the first column of a block of tabular data and returns a designated column from that row. The fourth argument of the VLOOKUP determines whether the lookup-value found has to be an exact match or a closest match.

 

Example of the VLOOKUP function

Example of the VLOOKUP function

So one example of a VLOOKUP is in the screenshot above, if the value in cell F2 is 105, the VLOOKUP function returns £5.00. As the lookup_value is 105, where that value is found in the first column of the table_array, the value, in this case from the fourth column is returned.

 

The VLOOKUP Function arguments

The VLOOKUP Function has three mandatory and one optional (range_lookup) arguments. Although the final argument is optional it is recommended that it is always used.

The syntax is as follows:

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

lookup_value – this is the value that you are going to use to locate within the first column of the table_array. This can either match exactly, or be a ‘closest match’ as long as the first column is in ascending order, and will then return the closest smaller value.

table_array – this is the location of the table_array. This is essentially a lookup table but the argument is called a table array as array formulas can used with this argument.

col_index_num – the first column from the table array is always column number ‘1’, the second column is column ‘2’ and so on.

Range_lookup – the name of the argument does not really indicate what this does. This is actually asking for either a ‘TRUE’ or ‘FALSE’. The argument is optional so if omitted, or is ‘TRUE’ or ‘1’ will return an exact or closest match. If ‘FALSE’ or ‘0’ is inserted only an exact match works. If a match is not found then a ‘#N/A’ error message is returned.

VLOOKUP function arguments

The VLOOKUP Function’s different arguments

Main issues to watch out for

Most people who have used the VLOOKUP function are aware that it can be very irritating. The main issues one comes across are:

  • The ‘exact match’ VLOOKUP function is very sensitive to errors. The usual issue is that the cell references are wrong. Sometimes there might be extra space in either the lookup_value or in the first column of the table_array.
  • The VLOOKUP function cannot look to the left of the first column only to the right.
  • When you are trying to SUM cells that are displaying an #N/A error message. The SUM function and other Maths function (or using sum in a Pivot Table) will not be able to ignore the error and will display the error message, try using the IFERROR or AGGREGATE Functions.
  • The VLOOKUP function is not case sensitive.
  • The first column in the table array has to contain unique entries.

The “exact match” VLOOKUP Function


The VLOOKUP can be used in many different ways. In this example the lookup_value is looking for an exact match in the first column of the table_array and if not found then the #N/A ‘error’ is returned.

The VLOOKUP function will use the company symbol/code as a lookup_value and seek out that value from another worksheet and then return the full name of that record.

TIP: When creating the VLOOKUP it is always easiest when you can see both the VLOOKUP function and also the table_array location, so utilities within the View tab, Window group such as Split, New Window and Arrange All are all very useful.

New Window and Arrange All commands

New Window and Arrange All commands

WARNING: Although the lookup_value is not case sensitive, it is not forgiving and if there are extra spaces somewhere in the values being looked for or within the lookup_value argument the VLOOKUP function will happily return the #N/A error.

TIP: The TRIM and CLEAN functions can be used by themselves, or as part of a larger VLOOKUP array function but that will be the subject of a future blog article.

Step 1: Open up the example file. If you want to follow along click here to download the example file.

Step 2: As the VLOOKUP function and table_array are on separate worksheets it would be useful to have them appearing side by side, therefore select View tab>Windows group>New Window and this opens up a new instance of this file.

Step 3: To get two windows side by side select View tab>Windows group>Arrange All, then select Vertical and OK and the windows appear side by side and further arrange the windows if required.

Arrange All window

Arrange All window

Step 4: Click into cell E2 of the VLOOKUP – Stock Prices worksheet, and type in “=VL” and the auto-complete pop-up is displayed.

Windows Side by side

Step 5: As the VLOOKUP function is the only function displayed it is selected, so either double left click or press the tab key from the keyboard.

Note: If you use auto-complete the last bracket the last bracket does not need to be entered, when you press <Enter> the final bracket will be filled in for you.

Step 6: The first argument is the lookup_value which is in cell “A2,” – notice that this value is in column A of the Company Names worksheet.

Step 7: Select the table_array from the Company Names worksheet by clicking and selecting from cell A2 to B18, and then press the F4 to make the cell range absolute so when we copy the formula the table_array does not move and then add a “,”.

VLOOKUP-Arguments-9

VLOOKUP-Arguments

Tip: With A2 selected press Cltr+Shift+right arrow, then Cltr+Shift+down arrow and that will select the cells need.
In this cell we are going to create a VLOOKUP function to return the full name of the company from the table array in the Company Names worksheet.

Step 8: The column required from the table is column 2 which will return the company name so add to the formula, “2,”.

Step 9: To finish the formula as the exact match VLOOKUP is required type in “FALSE)” and press <Enter>.

VLOOKUP-Arguments-11

VLOOKUP-Arguments-11

Step 10: Select the cell E2 again and hover the mouse over the bottom right corner of the cell and when the mouse pointer changes to a cross hairs, either double left click and the formula will be copied to the bottom of that set of tabular data or click and drag to the end of the data and your VLOOKUP function has been created.

VLOOKUP-Autofill-8

 

Things to watch out for

  • The #N/A error appears when an exact match cannot be found for the lookup_value
  • The #N/A error appears where there is something wrong with the cell reference for either for the lookup_value or table_array, or the col_index_num is out of range
  • When you are trying to SUM cells that are displaying an #N/A error message, the SUM function and other Maths function (or using sum in a Pivot Table) will not be able to ignore the error and will display the error message, try using the IFERROR or AGGREGATE Functions to avoid this issue

The “closest match” VLOOKUP Function

The ‘closest match’ VLOOKUP works in a similar fashion to the exact match VLOOKUP, except it is able to return a value which is an exact match or smaller than the lookup_value looked for. In the example below a lecturer has a series of marks for students and has to assign grades to the students based on a lookup table and the grades in the second column will be returned.

Note: There is no need for a table array with marks from 0 to 100 in the first column. Every mark in the first column from 0 to 49 will return a “Fail”, every mark from 50 to 59 will return “E” and so on.

Using the closest match VLOOKUP, the first column has to be in ascending order so if the mark is 72, the row containing the closest, lower match is selected and in this case the second column value of “C” is returned.

Step 1: Open the example file. If you want to follow along Click here to open the example file

Step 2: Select cell C3 and type in “=VLOOKUP(“ .

Step 3: The lookup_value is cell B3 so click on that cell and type in “,” so the function reads “=VLOOKUP (C3,” .

closest_match_vlookup

Closest match VLOOKUP function

Step 4: The table_array argument is the range of cells for the lookup table is, so select (click and drag) from cell G2 to H8 .

Note: We are going to be copying the function down column B. When we have completed the function so we need to make the table_array reference so that as we copy the function the table_array references do not move.

Step 5: Now press the F4 key which will make the table array absolute which is indicated with the $ signs being displayed, and add a comma so the function now reads “=VLOOKUP (B3,$G$2:$H$8,” .

Note: Do not include the column headers for the table_array, you only need the actual data.

Step 6: The column we want is the second column containing the grades, so add a “2” to the function so it reads “=VLOOKUP(B3,$G$2:$H$8,2” .

Step 7: The final argument is optional and will be a closest match VLOOKUP if it is not added. How so it is clear what the VLOOKUP is trying to do add “,TRUE)” and press <Enter>.

Step 8: The VLOOKUP should read “=VLOOKUP(B3,$G$2:$H$8,2,TRUE)” and return an “E” grade.

closest match vlookup arguments

closest match vlookup arguments

Step 9: From here, hover the mouse over the bottom right corner of cell C3. When the cross hairs is displayed click and drag down to the end of the column.

Things to watch out for

  • The N/A ‘error’ only appears when the lookup_value has a smaller value than the smallest value in the first column of the table_array.

The advantages using this type of VLOOKUP function are that

  • It is great for working out tax and financial lookups.
  • You do not need a huge table_array with 101 rows of data that you would need if you were using an exact match.
  • If you entered the grades in manually, what happens if the parameters of the marks to grades change?

Alternative VLOOKUP Solutions

  • Using IFERROR with VLOOKUP – this avoids issues with the N/A error.
  • INDEX & MATCH Function  – this is slightly more complicated but very flexible.
  • Using Tables with VLOOKUP  – Tables are dynamic so if the table_array changes so does the references dynamically.
  • Using VLOOKUP & MATCH – this makes the VLOOKUP easier to use with the Col_index_num.
  • Using VLOOKUP & Data Validation – This makes the VLOOKUP easier to use .
  • VLOOKUP Functions can be used with wildcards – adds more flexibility when looking up text.
  • Using MATCH, EXACT & INDEX for a case sensitive lookup – useful combination as the VLOOKUP is not case sensitive.
  • Using the VLOOKUP with the COLUMNS function – useful when using the VLOOKUP over many columns.

    0 Comments

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>