Excel Multi-Criteria Lookup
July 28, 2020
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.
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.
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:
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
To learn more about these formulas, and which one to choose, go to the Excel Lookup Multiple Criteria page on my Contextures site.
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?
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)
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.
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.
Last updated: April 4, 2021 8:58 PM