We will continue our quest for the best lookup formula with another combination. We will use an OFFSET and two MATCH functions to do a matrix lookup. Unfortunately, this formula also only searches from the right column to the left and from the top to the bottom, but is very easy to understand and use.
Practice File:
Transcript
In this and the following lesson, the knowledge of the simple syntax of the functions mentioned, is considered a perquisite.
We are trying to find the number of units sold on January 20th of 2015.
In cell J3 we type the offset function with reference cell the first cell of our table which is A1.
We will use the first match function to find the row number and the second to find the column number.
We type the name of the function, the lookup value in cell J2 and the lookup array (cells A2:A43). We are looking for an exact match. Then we continue to the next match function for the column number. We type the function again and the lookup value in cell J1. The lookup array is A1:H1 and we are again looking for an exact match.
Take note here that to calculate the right column number we have to subtract one from the result of the match function since the reference cell of the offset function is part of the match lookup array.
The next two attributes of offset can be ignored since we are looking for a single cell.
We have the required result which is 40.
This combination seems a lot easier to understand and use from the ones we have seen so far, at least to me.
But it has its limitations. First we have to remember to subtract the number one from our match functions if the reference cell of the offset function is part of the lookup array of the match function, and second we cannot lookup from right to left. We cannot look for example at what date we had a sale of 500 units. Not without needlessly complicating our formula at least.
Luckily there is another way that removes all limitations and complications and seems, at least to me, a lot easier to understand. In the following lesson we will see the end of our quest for the best lookup function where we will explain how index and match can be combined to the best lookup formula in excel.