Contextures

Contextures News 2020-07-28

Excel Multi-Criteria Lookup

July 28, 2020

Pivot table trick, multi-criteria lookup, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

You'll get my next newsletter on August 11th -- we're on the summer schedule now, with a newsletter every 2 weeks.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Multi-Criteria Lookup

Based on this table (tblProducts), what formula would you use to find the Code for a Jacket, in size Large? There are a couple of solutions below the screen shot, and you can download the formula challenge file to work on your own solution.

product list in named table

What solution did you use? Here are a couple of options:

Ifyour version of Excel has dynamic arrays (Office 365), you can use the new FILTER function:

  • =FILTER(tblProducts[Price], (tblProducts[Item]=A2) * (tblProducts[Size]=B2))

If you have an older version of Excel, you could use an INDEX/MATCH formula. It needs to be array entered, so press Ctrl+Shift+Enter

  • =INDEX(tblProducts3[Code], MATCH(1, (A2=tblProducts3[Item]) * (B2=tblProducts3[Size]),0))

To learn more about these formulas, and which one to choose, go to the Excel Lookup Multiple Criteria page on my Contextures site.

Pivot Table Trick

Most Excel users know that if you double-click a pivot table value, Excel creates a new sheet, with all the underlying data for that value.

But do you use this pivot table double-click trick?

  • Point to the top border of a pivot table row or column heading
  • When you see the black arrow, double-click, to open the Field Settings dialog box.

Excel Articles

Here are a couple of Excel-related articles that you might find useful or interesting.

Hard Work: Sometimes a small amount of programming, or other types of Excel work, can take much longer than expected. Mark Lacey explains this problem, in his "Why Did It Take Two Days" article. (Level - All)

Documentation: Whether you're writing a technical blog post, or documentation for an Excel project, these tips from Sandy Maguire can help you make it easier to read and understand. (Level - Int/Adv)

Also see: My Excel Products || Previous Issues

Stormy Day

I hope everyone who's in the path of the current tropical storms and hurricane stays safe! Aside from the occasional blizzard, we don't get too much severe weather in this part of Canada. That's why it was surprising to hear an emergency tornado alarm, blaring on our phones last week. Fortunately, no tornado appeared, but we did get heavy rain and strong wind for a while. There's a short video on my Debra D blog, with a couple more photos.

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200728ctx.html
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
dsd@ contextures.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info

 

Related Links

Lookup Multiple Criteria

Pivot Field Settings

Debra D Blog

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: August 9, 2020 3:30 PM