USING THE EXCEL VLOOKUP FUNCTION

VLOOKUP is a function which allows you to lookup unique data. The premise is to look for a unique identifier in the first column and return data in the same row in another column. The Excel VLOOKUP which is short for vertical lookup, would have to be one of the most used Excel functions.

The VLOOKUP syntax has four arguments.

=VLOOKUP (Lookup Value, Range, No Cols Offset, True/False)


Reference - the cell or text or value you are looking.

Range- the range that reference is in contained within. The Reference must be contained in the very first column as the data is looked up from left to right.

No Cols Offset - from the reference column

True/False -This is the type of match - either exact or approximate.


Key Point - VLOOKUP Only Looks To The Right!

VLOOKUP has two arguments for matching data, exact match and approximate match. The choice for either of the two is made with the 4th argument. It is True False above but Excel calls this [Range Lookup]. We put a False (or zero) to make Excel get an exact match or True (or One) for an approximate match.

Example 1 - The VLOOKUP Exact Match

Probably the most well known and used method for VLOOKUP is the exact match. This type of match is ideal especially when you are looking up a unique key and want to return data based on that key.

Vlookup formula excel

Once you have identified your unique identifier you can look up any column to the RIGHT of the column you are searching for.

The column Offset is 6 columns remembering to include the first column in your count.

Vlookup options

The syntax to a lookup for the above would look like the following.

=VLOOKUP(H2,$A$2:$F$10,6,0)

Vlookup in Excel

Where H2 is the Lookup value 3034292, the Range Lookup is A2:F10, the column offset is 6 and the type of lookup is an Exact lookup 0 (this can also be FALSE but why type 5 characters when 1 will do).


To return the year for the transaction the only thing we would change would be the Column reference. This would be 5.

=VLOOKUP(H5,$A$2:$F$10,5,0)


So the VLOOKUP would work on any of the columns to the right of the first instance of the saleskey above. It is important to remember with VLOOKUP that if your lookup key is not unique the first instance in the list starting from the top will be returned. The other instances will not be considered.



Example 2 - The VLOOKUP Approximate Match

Vlookup true criteria


The approximate match with VLOOKUP is not used as commonly however is exceptionally useful. It provides the opportunity to group data and search within bands for a particular result. One such example is grades in school, where grades are given inside certain scores. From 60-75 might be considered a C grade.

The formula is C2 is as follows:

=VLOOKUP(B2,$E$2:$F$6,2,1)

Where B2 is the score, E2:F6 is the range of the tables with the banded grades, 2 is the column to offset by and 1 (or True) for the last argument which is for an approximate match.