Today I learnt something new in Microsoft Excel, and I feel I should share it with you.
You know it’s pretty easy to lookup a value using VLOOKUP and HLOOKUP depending on the direction you want to lookup. Even more efficient is the INDEX/MATCH combination. But what happens when I have a table with row and column header and I want to find the intersection of a row and column? How do I achieve this?
Of course with a combination of VLOOKUP and MATCH (and also INDEX/MATCH) you could achieve this, but the trouble of writing these formulas is just not desired at all. After you learn this method, you’ll love two-way lookups.
Two-Way lookup using SUMPRODUCT
The easiest method I believe to do two-way lookups is by using SUMPRODUCT. Let’s say I have a distance chart of major Nigerian cities (see picture) and I want to find the distance between two cities, say, Ikeja/Lagos and Akure.
By using SUMPRODUCT to generate zeros but a one at the intersection, you can just combine this with the full table as second argument to get the data.
- a = row header
- b = row target
- c = column header
- d = column target
- e = full table range
Hopefully this helps someone get that data easier. If you have any comments, please drop them down below.
SideNote: For more methods of getting this same result, check out Chandoo’s article on two-way lookups here