• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Factor 15 The Lazy Lookup

You are here: Home / Excel Formulas / Excel Factor 15 The Lazy Lookup
Excel Factor 15 The Lazy Lookup
September 19, 2012 by Mynda Treacy
This Excel Factor tip was sent in by Kathy Carmel of Santa Barbara, California.

Words by Mynda Treacy.

Kathy uses Excel’s intersect operator to quickly SUM a cell, or range of cells at the intersection of two ranges.

You’re forgiven if you’re thinking I’ve just swallowed a load of Excel syntax, so let me show you an example.

I call this technique the Lazy Lookup, and there’s nothing wrong with being lazy, or perhaps you might like to call it efficient, as it sounds, well, less lazy 🙂

Lookup a Single Cell

We’ll take this table of data:

Excel intersect operator

And let’s say I wanted to find the value for T-Shirts for FY2011; I could simply enter this formula:

=D2:D8 B3:E3

=2111

Obviously I could also enter =D3 but then I'd need to know that that was the cell reference, which is not usually the case when you're doing a lookup, so we’ll ignore that for now.

Notice how there are no brackets in the formula (you don’t need them to lookup a single cell), and there is a space between the two ranges?

This space is the ‘intersect operator’, and it instructs Excel to find the value in the cell at the intersection of the two ranges, which is D3.

You could also use this formula to return text, since there is only one cell at the intersection.

Intersection of Named Ranges

While the above example works, it’s a bit laborious to enter the cell ranges, and since this is a lazy lookup we need to make it quicker and easier to enter.

To do this I’ve set up the following named ranges for each column and row in my table:

FY_2009 =B2:B8

FY_2010 =C2:C8

FY_2011 =D2:D8

FY_2012 =E2:E8

Dresses =B2:E2

T_Shirts =B3:E3

And so on…

Tip: I set up all these named ranges in just a few steps:

  1. Highlight the table, including headers
  2. CTRL+SHIFT+F3 to create named ranges from selection. This opens the following dialog box:
  3.  Excel intersect operator

  4. Click OK

Now I have my named ranges set up I can write the formula above like this:

=T_Shirts FY_2011

=2111

Again, no brackets, just a space between the two names. I think you’ll agree this now meets the ‘lazy’ requirement quite nicely.

SUM Multiple Cells

What say you wanted to SUM FY2010 and FY2011 for Skirts and Shorts?

You could use this formula:

=SUM(FY_2010:FY_2011 Skirts:Shorts)

 Excel intersect operator

Notes:

  1. Because there are multiple cells at the intersection you must wrap your named ranges in a SUM function, or AVERAGE, MIN, MAX etc.
  2. Also, you cannot return text where there are multiple cells at the intersection.

Non-contiguous Ranges

If you want to return the SUM of non-contiguous ranges simply group each set of rows and each set of columns together inside brackets like this:

 Excel intersect operator

Make it Interactive

Now for the ultimate in laziness, get someone else to build the formula by giving them data validation lists and use the INDIRECT function to build the formula on the fly.

Excel intersect operator

Note: the figures used in this example are fictional (I created them using the RANDBETWEEN function); I would never accept a reduction in my clothing budget year on year. 😉

Thanks to Kathy for sharing the Intersect Operator with us.

Kathy Carmel lives in Santa Barbara, California.

"I work with various forms of health care data, program in SAS and use Excel for deliverables and transitioning data to non-programmers. I have been using Excel for many years, mostly at a relatively basic level, but am now writing macros to do more of the analysis in Excel."

Vote for Kathy

If you’d like to vote for Kathy's tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment to thank Kathy for taking the time to share this tip….or all of the above 🙂

Excel Factor 15 The Lazy Lookup

More Excel_Factor Posts

Excel Factor Voting Roundup

Excel Factor Voting Roundup

Excel Factor 21 Hyperlink Triptych

Excel Factor 21 Hyperlink Triptych

Tips and tricks with hyperlinks to make them dynamically update as the selection in the sheet changes. Sample workbook available.
Excel Factor 20 Custom Number Format Disguise

Excel Factor 20 Custom Number Format Disguise

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

The Excel HYPERLINK function creates links to places inside or outside of your Excel file. I'll show you a shortcut for creating them that few people know.
Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 16 Dynamic Lookup

Excel Factor 16 Dynamic Lookup

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 13 Handy Tips & Tricks

Excel Factor 13 Handy Tips & Tricks

Excel Factor 12 Secret EVALUATE Function

Excel Factor 12 Secret EVALUATE Function

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel FormulasTag: Excel_Factor
Previous Post:Excel Factor 14 Interleave Data from Two Columns into OneExcel Factor 14 Interleave Data from Two Columns into One
Next Post:Excel Factor 16 Dynamic LookupExcel Factor 16 Dynamic Lookup

Reader Interactions

Comments

  1. Sheeloo

    January 20, 2020 at 6:06 pm

    Thanks Mynda and Kathy.

    I believe ‘lazy’ users of Excel are the best users… as they keep trying to find a faster, better way of doing things, push limits of Excel…

    It is a GREAT thing that these ‘lazy’ people are NOT lazy when it comes to sharing their findings… so thanks all those out there who love to share.

    Reply
    • Mynda Treacy

      January 21, 2020 at 2:44 am

      🙂 glad you agree.

      Reply
  2. Dave Kerr

    June 29, 2017 at 11:53 pm

    This tip is brilliant. I had never come across the use of the space to select the intersection of cells.
    I had one issue when preparing the named ranges: Excel tagged an underscore to the end of the Year values so the Indirect wouldn’t work. I changed the values from “FY2009” (which became “FY2009_”) to “FY_2009” and then derived the range names. Perfect!

    Reply
    • Mynda Treacy

      June 30, 2017 at 10:38 am

      Glad you liked it, Dave 🙂

      Reply
  3. Marian Stasney

    April 7, 2016 at 3:39 am

    Quick question: when you used CTRL+SHIFT+F3 to create named ranges from selection, I assume you were working on a standard keyboard. I use a laptop. Would I also need to use the FN key to select the F3 function?

    Great tip!!! Keep them coming!
    Thank you
    mks

    Reply
    • Mynda Treacy

      April 7, 2016 at 7:53 am

      Hi Marian,

      Yes, you would have to press FN as well if you don’t have dedicated function keys on your keyboard.

      Mynda

      Reply
  4. Chandru Parthiban

    July 25, 2015 at 4:46 pm

    Kathy, Very Nice & Useful Tips,

    With Thanks and Regards,
    Chandru Parthiban

    Reply
  5. Gordon

    March 19, 2014 at 12:04 am

    Hi Mynda,

    I use intersection of named ranges across multiple workbooks.
    Each workbook being identical in structure.

    I am hoping to find most efficient syntax for formula like:
    Book1.xlsx! Qtr1 Sales + Book2.xlsx! Qtr1 Sales + Book3.xlsx! Qtr1 Sales

    But seems like I have to type:
    Book1.xlsx!Qtr1 Book1.xlsx!Sales + Book2…….

    Is there a more efficient syntax than above ?

    Many Thanks,
    Gordon.

    Reply
    • Mynda Treacy

      March 20, 2014 at 2:07 pm

      Hi Gordon,

      I’m not sure what the intersection of named ranges has to do with your formula as it looks like you want to sum/add ranges. If so, you can use a formula like this:

      =SUM([Book1.xls]’Qtr 1 Sales’!$B$2:$B$20,[Book2.xls]’Qtr 1 Sales’!$B$2:$B$20,[Book3.xls]’Qtr 1 Sales’!$B$2:$B$20)

      Note: Substitute $B$2:$B$20 for your named range if desired.

      I hope that helps. I’m not aware of any simpler formulas.

      Kind regards,

      Mynda.

      Reply
  6. khalid

    November 14, 2013 at 5:14 pm

    excellent !!

    Reply
    • Mynda Treacy

      November 14, 2013 at 8:33 pm

      🙂 thanks, Khalid.

      Reply
  7. Khurram

    November 13, 2013 at 9:15 pm

    understood and thanks for nice tip.

    Reply
    • Mynda Treacy

      November 13, 2013 at 9:26 pm

      You’re welcome, Khurram 🙂

      Reply
  8. Subash

    April 6, 2013 at 8:36 pm

    Dear Mynda,

    I tried the above with exact details as shown above and made the data validation lists and put in the formula =SUM(INDIRECT(B15) INDIRECT(B16)) result I am getting is null error.

    You have two hidden rows (10,11) in your example above. Is there anything there?

    Kindly advise
    Regards. Subash

    P.S. All the comments below were posted on my birthday last year. -:)

    Reply
    • Carlo Estopia

      April 8, 2013 at 3:50 pm

      Hi Subash,

      What post are you referring to?
      What example and what file?

      Cheers,

      CarloE

      Reply
      • Subash

        April 8, 2013 at 5:21 pm

        Hi Carlo,

        “Make it Interactive” in
        “Excel Factor 15 The Lazy Lookup”

        Rgds,
        Subash

        Reply
        • Carlo Estopia

          April 8, 2013 at 8:06 pm

          Hi Subash,

          Believe me nothing is hidden in there. lol.

          Now, I also tried to walk-through this and we got the same error.
          By luck however I followed the named range to the letter: FY_2009, FY_2010, FY_2011, FY_2012.
          I surmise you also skipped the underscore(“_”)because we are both ‘lazy’? lol.

          Cheers,

          CarloE

          PS: If this don’t work, don’t ask me again. Ask Mynda. 😛

          Reply
          • Subash

            April 11, 2013 at 12:32 am

            Hi Carlo,

            It did work, but in my file the automated name range was FY2009_ and not FY_2009. Lazy eh !

            Thanks very much.
            Regards.
            Subash

          • Carlo Estopia

            April 11, 2013 at 1:37 pm

            Hi Subash,

            I’m the only one who’s lazy. 😉
            Anyway, but you still have the underscore.

            Cheers,

            CarloE

          • Subash

            April 12, 2013 at 9:15 pm

            Carlo,

            Hi, we all are lazy that is where Excel comes in……
            Keep up the good work of sharing knowledge my brother.

            Regards,
            Subash

  9. Phil

    September 20, 2012 at 5:36 pm

    amazing tip, wasn’t aware of the Intersect operator. And again: another lovely use (or lazily use?) of named ranges… Thanks for this tip…

    Reply
    • Mynda Treacy

      September 20, 2012 at 5:55 pm

      🙂 Cheers, Phil.

      Reply
  10. RAVI XAVIER

    September 20, 2012 at 2:18 pm

    THANK YOU VERY MUCH

    Reply
    • Mynda Treacy

      September 20, 2012 at 3:57 pm

      You’re welcome, Ravi 🙂

      Reply
  11. r

    September 20, 2012 at 6:24 am

    i’m agree with you … “there’s nothing wrong with being lazy” … on the contrary 🙂
    nice article, for me particularly interesting the section “Non-contiguous Ranges”
    regards
    r

    Reply
    • Mynda Treacy

      September 20, 2012 at 6:38 am

      Thank you, r!

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x