Using VLOOKUP + MATCH (HLOOKUP + MATCH, OFFSET + 2 MATCHes or INDEX + 2 MATCHes) to search a table in both axis.
This post has been sitting for a while in my archive, waiting for me to get some time to polish and post.
Back in 1997, one of the tricks I learned and was the reason for me to start loving Microsoft Excel, is this …
Combining two (or more) functions to search a table in two dimensions.
We can use…
VLOOKUP + MATCH
Since VLOOKUP can search vertically (the V in VLOOKUP) we just need to use another function to search horizontally.
MATCH fits perfect because it returns the number of cell returned, which exactly what VLOOKUP needs
HLOOKUP + MATCH
In similar fashion, HLOOKUP will search horizontally (H in HLOOKUP), so we need a function to do the search in vertical. Yes, it is MATCH again, perfect match
INDEX + 2 MATCHes
INDEX function does not do any search, it is basically calling the X and Y for a table, that is why we need 2 MATCHes, one vertical, and the other horizontal.
OFFSET + 2 MATCHes
OFFSET, is one of the most powerful formulas in Excel, I used it a lot (along with INDIRECT, ADDRESS, etc) to do bunch of things, here we will see how it helps us extracting from table, with the help of 2 MATCHes
We will use same table and same reference cells to show the similarities (and differences) among those 4 ways to do the search.
So, if we have our table in Sheet1, in cells B2:H22, full reference will be Sheet1!$B$2:$H$22
Then we put the ID we want to search for in vertical in Sheet2 cell B3
The column name we want to search for in Sheet2 C2
Then in C3 will be one of those formulas:
=Vlookup($B3, Sheet1!$B$2:$H$22, Match(C$2, Sheet1!$B$2:$H$2, 0), False)
=Hlookup(C$2, Sheet1!$B$2:$H$22, Match($B3, Sheet1!$B$2:$B$22, 0), False)
=Index(Sheet1!$B$2:$H$22, Match($B3, Sheet1!$B$2:$B$22, 0) +1, Match(C$2, Sheet1!$B$2:$H$2, 0) +1)
=Offset( Sheet1!$B$2, Match($B3, Sheet1!$B$2:$B$22, 0) -1, Match(C$2, Sheet1!$B$22:$H$2, 0) - 1, 1, 1)
Now that we see the 4 combination, we can easily find differences (and similarities) among them.
To describe those in English, the search funcions are VLOOKUO, HLOOKUP and MATCH.
While INDEX and OFFSET do not do any search, that is why we needed 2 MATCHes with each, while we inly needed one MATCH with VLOOKUP or HLOOKUP.
