vlookup(), match() and offset() – explained in plain english [spreadcheats]
VLOOKUP may not make you tall, rich and famous, but learning it can certainly give you wings. It makes you to connect two different tabular lists and saves a ton of time. In my opinion understanding VLOOKUP, OFFSET and MATCH worksheet formulas can transform you from normal excel user to a data processing beast.
Today, lets understand how to use these formulas better.
What is the syntax for Match, Vlookup and Offset?
Here is the syntax for these three very powerful functions in plain English:
What are vlookup () and match () ?
VLOOKUP and MATCH are your way of asking excel to find a needle in haystack. Imagine you have all your customer contact information in one sheet in the range A1:D5000 in the format phone number, name, city and date of birth. Now you need to find out which customer has the phone number “936-174-5910”. How do you do it?
You guessed it right, you use VLOOKUP and summon excel to do the search and return with customer name.
While VLOOKUP is used to fetch value a based on what you are looking for, MATCH is used to fetch the position of the value you are looking for.
See this illustration to understand :
What does VLOOKUP really do?
Imagine you have a list of data like this:
Now, how do you answer the question – “How many sales did Jimmy make?“
Yes, your guess is right. VLOOKUP is one of the formulas you can use to answer questions like this.
VLOOKUP searches a list for a value in left most column and returns corresponding value from adjacent columns.
So, in our case, we need VLOOKUP to search for Jimmy and return the amount of sales he made from column 3.
VLOOKUP Syntax & Examples:
The syntax of VLOOKUP is simple:
=VLOOKUP( this value, your data table, column number, optional is your table sorted?)
Here is an example to get you started:
VLOOKUP Examples & Homework
I have made a small excel file detailing 4 VLOOKUP formula examples. The file also contains some home work so that you can practice this formula.
So what is Offset() then?
OFFSET is your way of telling excel to fetch a portion from large range of values. You can compare OFFSET to what you see from your car window while driving. As your car moves, you see different things from the window.
OFFSET returns a reference to the portion of a large range you have supplied based on 5 parameters. For eg. OFFSET (A1, 3,4, 5,6) would return 5×6 cell range from E4 (A+4 columns, 1+3 rows = E4) thus: E4: J9
See this illustration to understand how OFFSET spreadsheet formula works:
Related: Learn more about OFFSET Formula.
So how are Offset() and Match() linked to each other?
Since MATCH returns the position of the item you are looking for in a list, you can then use this position in OFFSET to fetch values surrounding the searched value.
Related: Using INDEX instead of OFFSET.
Remember, both VLOOKUP and MATCH throw a fail error of #value! if the value you are looking for is not there. Also, OFFSET returns a range so make sure you pass the value to another function like SUM that accepts ranges.
Just use them with some dummy data, play around with arguments and see how you can say “oh yeah, I can do that in few minutes” to your boss next time.
Want to Learn More Formulas? Get my VLOOKUP book
If you want to learn VLOOKUP and other Excel lookup functions, then consider getting my VLOOKUP book.
Leave a Reply
|The new Mac vs. PC web ad by Apple is really cool||Generating Random Phone Numbers|