Pinterest Pixel

Two Way Lookup Using The SUM Intersect Function

There are various lookup functions that bring back values from a range of data like the VLOOKUP,... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

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:

See also  INDIRECT Function for Dependent Dropdown Lists in Excel

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 itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

See also  LARGE Formula in Excel
Founder & Chief Inspirational Officer at MyExcelOnline.com

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 Academy Online Course.

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!