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…


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

highlight data


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

create from selection


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:

create names dialogue box


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

Name Manager

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):

named range confirm


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


sum 1st argument


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


2nd argument


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


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

Sum Interesction


How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Consolidate with 3D Formulas in Excel 3D Formulas or References in Excel are a great way to consolidate data from multiple sheets. 3D Formulas reference several worksheets that have the same structure which allows you to consolidate by using the SUM function. Formula breakdown: SUM(Sheet1:Sheet4!A1) ...
Jump To A Cell Reference Within An Excel Formula When writing, editing or auditing Excel formulas you will come across a scenario where you want to view and access the referenced cells within a formula argument. This is helpful if you want to check how the formula works or to make any changes to the formula. There is ...
Summarize Data With Dynamic Subtotals What does it do? It returns a Subtotal in a list or database Formula breakdown: =SUBTOTAL(function_num, ref1) What it means: =SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data) ***Go to the bottom...
Match Two Lists With The MATCH Function I am sure that you have come across many occasions where you have two lists of data and want to know if a specific item in List1 exists in List2. Well I have! With the MATCH function you can verify if a cell´s item in List1 exists in List2. The function will return the r...