In this tutorial we are going to look at how we can do a lookup in an array by looking up the values in the far right column and returning the result from a column to the left. We are going to use the OFFSET and MATCH functions to accomplish this.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Normally, when you do a lookup using VLOOKUP, you need to look in the far left column and return your result from a column to the right. We were able to do a reverse lookup using VLOOKUP in conjunction with the CHOOSE function and Array Constants in a previous blog post that you can find here. In this blog post, we are also going to do a reverse lookup but using OFFSET and MATCH.
Here I have a data range that has a date in column A, letters in column B and values in column C. I want to transfer the data into the table that is located in H1:M7:
First I am going to insert a helper column in column D that concatenates the date and letter into one cell that I’ll use as my lookup value:
Now, let’s take a quick look at the OFFSET and MATCH functions that we are going to use.
OFFSET: “Returns the reference to a range that is a given number of rows and columns from a given reference”
The syntax for OFFSET is: =OFFSET(reference, rows, columns, [height], [width])
So with OFFSET, you start with a reference point or an anchor point, then determine the number of rows down and columns across you go, and it will return the value from that cell. If you want the result to be a range, you use the optional arguments to define the number of rows high and columns wide you want it to be. OFFSET defaults to one row high and one column wide, and since that is what we want, we won’t use those optional arguments.
MATCH: “Returns the relative position of an item in an array that matches a specified value in a specified order”
The syntax for MATCH is: =MATCH(lookup_value, lookup_array, [match_type])
In this case, we tell the MATCH function to look up a certain value, we tell it where to look it up (array) and if we want it to be an exact or approximate match. We will use this to determine the row for the OFFSET function.
The formula that we will enter in cell I2 will be:
Cell $A$1 is our reference point (or anchor point) for our OFFSET function.
The MATCH function: MATCH($H2&I$1,$D$1:$D$30,0)-1 looks for the concatenated date and letter in column D to tell the OFFSET function how many rows down to go. However, since we are starting with cell $A$1, which is within our data set, we need to subtract one from that result so we get to the correct row. The “2” tells the OFFSET function to go over two rows from our anchor point of $A$1 which takes us to column “C”, which is where the values we want to populate our table with reside.
And once copied, the result is a nicely populated table:
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!