The VLOOKUP function

The VLOOKUP function is a lookup and reference function which performs a similar task to that of its cousin HLOOKUP, but searches the leftmost column of the selected range for the searched value instead of the top row. You can then set which column you would like to return your results from.

This is the format of the VLOOKUP function:

=VLOOKUP(SEARCHED VALUE, SEARCH RANGE, RETURN COLUMN, RETURN TYPE)

Here is an example of the VLOOKUP function:

=VLOOKUP(“Canada”, A1:G12, 5, FALSE)

VLOOKUP function returning the value from the 5th column, in the row whose country matches ‘Canada’

This function is valuable when there are unique or key variables in the range you would like to search, where the important information to return is located to the right of the initial column. Most people and companies will naturally organize their data in a way which supports to some degree the format required for a VLOOKUP. In cases where it does not, the INDEX function becomes more relevant.

An example of a functional implementation of this would be creating a reference table for a set of goods or services listing all of their key pieces of information, with something like their UPC or product code being in the leftmost column. You can then generate all the information about whichever product you select using VLOOKUP. This is great for something like an invoicing template.

ex: Product codes are in column A, other information is in columns B through F

=VLOOKUP(PRODUCT CODE, $A:$F, 2, FALSE)