Contextures News

Excel Lookup Formula Challenge

Oct 17, 2017

Formula challenge, Happy Spreadsheet Day, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

Spreadsheet Day

Happy Spreadsheet Day! I started this annual event in 2010 -- go to my Spreadsheet Day Blog to learn more about it. We celebrate on October 17th, because that's when the first copy of VisiCalc shipped, back in 1979. Did you ever use VisiCalc, or an early version of another spreadsheet?

Remember to take a couple of minutes today, to say thanks to the creators of spreadsheets, and enjoy another great day of working in Excel.

Lookup Formula Challenge

Here's a lookup formula challenge, if your brain needs some exercise.The problem is based on an email question that I got last week, and it was fun trying to find an efficient solution.

• In columns A:B, there is a list of customers and their ID codes.
• In column D, there is imported data. There is a customer name in each cell, but not in a consistent place.
• Create a formula in column E, to get the correct customer code.

To try the challenge, build your own sample based on this screen shot, or download my Excel file. The file has the challenge on the first sheet, then a sheet with a couple of clues, and a solution sheet.

Did you come up with a similar formula, or something different/shorter/better?

Quick Tip

In Excel 2016, there's an automatic date grouping feature for pivot tables. If you don't like that feature, here's how to turn it off.

• Click the File tab at the top of Excel, then click Options
• At the left, click Advanced*, then scroll down to the Data section (near the bottom)
• Add a check mark to “Disable automatic grouping of Date/Time columns in Pivot Table", click OK.

*If you see a “Data” category in the list at the left, click that, instead of Advanced.

Thanks to Van, who asked about this setting, and learn more about pivot table grouping on my website.

Excel Articles

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

Excel AMA - Do you have questions that you'd like to ask Microsoft's Excel team? Good news -- you can do that tomorrow, Wednesday, Oct. 18th. Go to the Excel "Ask Me Anything" (AMA) session, starting at 12:30 pm Eastern Time, on the Reddit website. When the AMA starts, use go to this Reddit page, and click the link at the top, to go to the discussion. (Level - All)

Excel Solver - How would you divide 24 people into 3 equal teams, based on their scores? See how to do that with Excel's Solver tool. Have a strong cup of coffee first! (Level - Int/Adv)

Also see: My Excel Products || Excel Events || Previous Issues || Excel Humour

Fall Colours

Be careful, if you step outside to take pictures of the lovely fall colours. I took this photo in our back yard, then discovered that my husband had locked up the house, and driven away! (He thought I was working in my upstairs office.)

Fortunately, I had my phone, and he came right back to let me in. And, as you can see, it was perfect weather for being stuck outside.

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

NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version: https://www.contextures.com/newsletter/excelnews2017/20171017ctx.html

Debra Dalgleish
ddalgleish @ contextures.com

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.