Want create site? Find Free WordPress Themes and plugins.

Hello people!

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.

SUMPRODUCT(($B$3:$B$34=B38)*($B$3:$AG$3=D38),$B$3:$AG$34)
SUMPRODUCT(($B$3:$B$34=B38)*($B$3:$AG$3=D38),$B$3:$AG$34)

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.

ECHO IT!

SideNote: For more methods of getting this same result, check out Chandoo’s article on two-way lookups here

Did you find apk for android? You can find new Free Android Games and apps.
SHARE

Leave a Reply