VLOOKUP and MATCH, the perfect couple

VLOOKUP and MATCH, the perfect couple…

A lot of you are probably familiar with VLOOKUPs, and a lot of you may even be familiar with the MATCH function, but did you know that you can combine them together and create magic?  Seriously, VLOOKUP + MATCH is like wine + cheese, Romeo + Juliet, or chips + salsa.

Suppose you have a spreadsheet with a lot of data.  Each order is unique and has the fields Plan, PM, Description, Cost, Rating, Value, and Quality associated with it.  For example:

Sheet1

Then suppose you want to transfer all this data to another sheet, but with only a subset of the above fields.  One way to do it is to set cell references.  But when you start cutting and pasting stuff, it will get confusing.  However, there is a way to use 1 formula to pull all the values that you need.  Here’s where the VLOOKUP + MATCH come in to play.  On a blank spread sheet (Sheet2), create the design for your output and choose the orders and fields that you want to display.

Sheet2

Then, you can write 1 formula to populate all the blank cells based on the order number and field title.  For example, in cell B2, write: =vlookup($A2,Sheet1!$A$1:$H$21,match(B$1,Sheet1!$A$1:$H$1,0),0)

Make sure that the arrays used in the VLOOKUP and MATCH function refer to the data sheet and not the sheet of your output.  This is important!

So, with the above formula, you can paste it into all the blank cells, and the formula will pull the data you need.  The VLOOKUP looks for the order number you specified, and the match function finds the specific field you are interested in.  Voila!  Same formula can be pasted repeatedly to pull different data points.  The locking that I have specified directs the formula to point at the relevant order number and field no matter where you paste the formula.

Keep in mind that you do not have to use the titles as your match range.  You can use a blank row above your titles and above your data to create a new match range.  E.g.

Data

Exhibit

In this case, you just set the MATCH range to the first row.  This allows you to set up your own naming convention for the MATCH function, so you’re not limited to the titles of your exhibit.  Just remember to reference your new MATCH range when you set the array for the MATCH function.

This VLOOKUP + MATCH concept is very powerful and useful for building exhibits, transferring data, or viewing data in a specific way.  It’s quick and efficient once you get the hang of it, and minimizes chances of error.

Here is a spreadsheet demonstrating my example.