Lookup: V-lookup and Beyond

Think for a moment if you had an entire library full of books and you needed to know the author of a specific book. Instead of walking around the library trying to find this one book, you would look up it's location using a computer or card catalog. Excel has lookup functions as well that make finding specific information from lists much easier. The most commonly taught lookup is the vlookup() aka Vertical Lookup. Other lookup functions we will use include Hlookup() aka Horizontal lookup, Match(), and Index().

VLookup has three required arguments and one optional argument.

vlookup(

Lookup Value -- The item you are looking for

Range -- The array of data you are looking in

Column -- which column number of the range you are retrieving data from

[Match Type] -- Approximate or exact match

)

Vlookup is both quick and powerful, however it is limited to requireing the lookup_value be in the first column.

If we wanted to know Sam's Tenure it would be:

=VLOOKUP("SAM",A1:D5,3,0)

This will return the third column and Row where Column A has "Sam"

If we wanted to know the Sales per month of Employee ID 30, we would need to change the Range.

=VLOOKUP(30,B1:D5,3,0)

If the value does not exist in the data set, you will get an error of #N/A. This is a great way to identify missing items when comparing two similar lists. The "Lookup_value" can be the contents of another cell. See the Tables below:

If we copy our formula from cell G2 down through G6, we can immediately sport that George is missing from the first list.

Hlookup is almost identical. Instead of searching the first COLUMN for your value, it checks the first ROW instead. It then returns the value from the cell however many rows below that, that you asked for.

Consider that you need to lookup the name associated with an ID #. Because the names are in a column before the ID Numbers, a VLookup will always give an error. A useful approach to this problem is the INDEX formula. Match will be another important formula as it provides the position of a given cell.

The Index Structure is: INDEX(Range, Row, Column)

The Match Structure is: MATCH(Value, Range, MatchType)

If we need to know Employee ID 30's Name we can use

INDEX($A$1:$D$5,MATCH(30,B1:B5,0),1)

as the value 30 is in row 4 of the B Column. The match "value" can be set to a cell reference so that you can change the lookup item at any time.

Multiple Match formulas can be used to look up both the Column, and Row.

In columns G and H we will setup the lookup. Range G1 will be a dropdown of the column headers.

INDEX($A$1:$D$5,MATCH(G2,B1:B5,0),MATCH(F1,A1:D1,0))

Because Tenure is selected in G1, the formula will return 5 years. If we change that to Name, "Frank" will show.

This can be quite useful if you have large data sets and need to look up limited information.