The VLOOKUP function is a life saver. It searches down the first column of a range for the key you want to find and returns the value of a specified cell in the row found. It does sound a bit opaque but by having a look at a specific example it will come all clear. Or at least that is the hope :). Let’s have a look at the formula first.
Let’s imagine we have a list of clients, defined by their Cust ids, and the corresponding amount they spent. Let’s imagine we want to retrieve the amount that a specific customer (Cust ID = 3) spent. In this case this is a trivial task as we have just a few rows of data, but you can imagine how VLOOKUP becomes powerful when dealing with 100’s or even 1000’s of rows. Anyway, below is the formula:
Now in details:
The VLOOKUP function takes four parameters as input: search_key, range, index and is_sorted.
=vlookup(3, A1:B6, 2, 0)
The search_key represents the value to search for. In our example it would be the Cust ID of 3.
Range defines the range to consider for the search and the value to be returned. The first column is always and by default the column used by VLOOKUP to find the search_key.
Index defines the number of the column where the value to be returned is located. In our case we want to know the revenue generated by Cust ID = 3, so we want to return the value in the second column of the range.
Is_sorted indicates whether the column to be searched is ordered or not. The value is usually FALSE (or 0) in most cases.