Vlookup

Vlookup searches a value (like an area label) in the first column of a table and retrieves the value of a specific column (like the seller related to selected area).

vlookup excelLet’s see a practical vlookup example.

We have two lists. The first is a list of Italian wines who belong to a specific geographic area. The second is a list of geographical areas and sellers that follow each specific area.

when use vlookup

We want to associate each wine to his own seller. The end result will be the following:

result

What is the common information to the two lists? The geographical area, associated with wines in the first list and with the seller in the second one.

The function will be:

= VLOOKUP (C3; G$3:H$5; 2; 0)

The logic to follow is: search the area of ​​Montepulciano d’Abruzzo (center) in the first column of the second list, when you find the area return the second column of that row.

how to write vlookup

If the value occurs more than once VLOOKUP gets the first row containing the value searched!

Below you can find the detailed explanation of each step.

How to write Excel VLOOKUP?

Move to the cell where you want to get the result. Call list of functions and choose VLOOKUP in the category Lookup & Reference (or from “Most Recently Used” if we just used the function or from the “All” category, where Excel functions are listed in alphabetical order).

The VLOOKUP expected that at least three parameters are specified:

 1)  the value to search (Lookup_value): in our case C3, the cell in which it is written the area

lookup_value

 2) the table where to look for the value (Table_array): in the example is G3:H5

table_array

 3) the number of column from which to extract the data (Col_index_num). In our case is 2, because 1 is the first column (Area) and 2 indicates the second column (Seller).

col_index_num

 4  The fourth parameter (range_lookup) is usually set to zero (or FALSE), this suggests to Excel that we want exactly the value we’re looking for, and in the absence of such value, we want the “unavailable result” ( #N/A). This is called “exact match“.

range_lookup

Our function will be:

= VLOOKUP (C3; G3:H5; 2; 0)

If we try to drag our results in the next cell, however, we get the error: The problem is that dragging down changes the reference to the table array.

vlookup not working

We solve blocking with the dollar the reference to the rows of the table array. We can then drag down and get the correct result.

= VLOOKUP (C3; G$3:H$5; 2; 0)

how to write vlookup

 

To summarize what has been done in the previous example,

we can say that the “value”, the first parameter, is searched in the cells of the first column of the “Table_Array”; once you find the first row containing the value VLOOKUP gets the information specified by the parameter “Col_index_num”, which represents the number of the column from which to get the data. The optional “Range_lookup” parameter should be set to zero or FALSE when you want to look exactly for the “value” and to show the message “not available” (#N/A) if it is not identified.

#N/A error message

When the VLOOKUP does not find the result we are looking for we get the message #N/A. To customize the #N/A error message you can use a simple logic function: IFERROR.

The first parameter of the function is the value that we want to verify, in our case the result of VLOOKUP.
The second parameter is the expression that we want to be written in case the first parameter is an error.

The syntax is:

IFERROR = (value; value_if_error)

In our example:

= IFERROR (D3; “missing information”)

iferror

The result is:

iferror result

In our case, instead of the cell D3 we can insert our VLOOKUP.

how to write iferror

This is the final result:

iferror vlookup

Vlookup in different sheets

How to do when the table array is located on a different sheet?

The only difference compared to the use that we have seen so far is in reference to the table array. When you are in the second parameter, you must move to the table array sheet (in this case named Area Seller) and select the table.

vlookup different sheets

Be careful not to return to the starting sheet, otherwise Excel records the movement and you lose the reference to the table array sheet.

How to do when there are spaces in table array?

When table array contains spaces our vlookup doesn’t work.

spaces in table array

The solution is: wilcards.

The * wildcard means “any character”, including no other character. You could replace “*” with a number of character from 0 to infinite.

So writing north* in value parameter means we are exactly looking for “north” or as an alternative “north” followed by any character, so “north area” or”north region” or “north___” followed by spaces.

In vlookup value we write C3&”*” in order to concatenate the value of C3 (“center”) with “*”: “center*”

=VLOOKUP(C3&”*”;G$3:H$5;2;0)

result with wildcards

The whole result is

result with wildcards

Writing “*north*” values matched could be for example “area north”, “north” or “north area”.

The ? wildcard means “one singular character”. Writing “north?” we are looking for a value of six letter, whom first five are “north”