Beyond VLOOKUP: OFFSET + MATCH = Insanity!

OK, so you are comfortable with VLOOKUP, but tired of counting columns to point the function to the data you are interested in. Wouldn't it be great to have the formula do the leg work and find the # of Columns or Rows to where your data lies?

I have the perfect formula for you, OFFSET. This formula allows you to set a reference cell and find data Relative to that cell.

We are using the same data set from the VLOOKUP example. Imagine we are tired of counting how many columns there are between Outlet and 1st Quarter Sales. We tell OFFSET to use Cell A6 as a reference. OFFSET will ask for the # of ROWS and COLUMNS to move from this reference cell.

Instead of simply counting the # of rows and columns, we embed the MATCH formula into OFFSET. MATCH is very simple and simply finds a cell the matches the data you input. In this example, we need to use the Outlet data to determine how many rows from the Reference cell to travel. Notice that the array we use with the MATCH function does not include any data. OFFSET only needs the number of ROWS we want it to move. The Outlet data is in Cells $A$7:$A$14. NOTE: Do NOT include your OFFSET Reference cell in the MATCH lookup_array. Use the number 0 for your match type. Using 0 will direct MATCH to find EXACTLY what you are looking for, just like FALSE in the VLOOKUP formula.

Repeat the above with another MATCH statement with the variable you are looking for, in this case, 1st Quarter Sales. This value must be EXACTLY as it is in your data array. I STRONGLY recommend copying this value from your data into your output sheet to assure you are looking for something MATCH will Identify. (Many raw data sets contain hidden spaces after the text).