Pinterest Pixel

Two Way Lookup Using The SUM Intersect Function

John Michaloudis
There are various lookup functions that bring back values from a range of data like the VLOOKUP, INDEX, INDEX-MATCH and OFFSET functions, just to name a few.

There are various lookup functions that bring back values from a range of data like the VLOOKUP, INDEX, INDEX-MATCH and OFFSET functions, just to name a few.

There is another lookup formula that you can use that will return the intersection of two Named Ranges!

This is done by using the SUM formula and entering a Named Range for the 1st argument and then pressing the SPACE BAR on your keyboard (thus activating the intersection) and entering a 2nd Named Range.

This combination will look at the intersection of the 1st Named Range and the 2nd Named Range and return that cell’s value.  I show you how below…

Two Way Lookup Using The SUM Intersect Function | MyExcelOnline
Download excel workbookSum-Interesction.xlsx

STEP 1: Highlight your data which has to have Column headings and Row headings, just like the table below:

Two Way Lookup Using The SUM Intersect Function

 

STEP 2: Go to the ribbon menu and select Formulas > Create from Selection 

Two Way Lookup Using The SUM Intersect Function

 

STEP 3: This will bring up the Create Names from Selection dialogue box.  Choose the Top Row and Left Column boxes, as this is where your headings are located and press OK:

Two Way Lookup Using The SUM Intersect Function

 

STEP 4: In the ribbon menu, go to Formulas > Name Manager to see the Named Ranges that were created in Step 3

Two Way Lookup Using The SUM Intersect Function

You can double click on a Named Range and click on the Refers to area to see the range (press ESC to get out of this screen):

Two Way Lookup Using The SUM Intersect Function

 

STEP 5: Enter the SUM function and in the first argument type in the 1st Named Range:

=SUM(EUROPE

Two Way Lookup Using The SUM Intersect Function

 

STEP 6: Enter a SPACE after the 1st argument.  Now type in the 2nd Named Range:

=SUM(EUROPE MAR)

Two Way Lookup Using The SUM Intersect Function

 

This will return the intersection of the 1st and 2nd Named Ranges:

Two Way Lookup Using The SUM Intersect Function

You can even make this formula interactive by inserting a drop down menu (data validation) for the Months and the Regions!

 

 

If you like this Excel tip, please share it


Founder & Chief Inspirational Officer

at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  VLOOKUP Function: Introduction

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...