vlookup(), match() and offset() – explained in plain english

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.

In our 4th installment of spreadcheats, we will learn how to use these formulas.

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 :

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:

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.

Finally

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.