Excel Formula Mystery
February 13, 2018
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
In a pivot table, you can use the heading drop downs, to show or hide items. For example, select a couple of products to show, and leave the rest hidden.
Instead of using that drop down list, it's sometimes quicker to use right-click menu:
Someone sent me a workbook in which a simple VLOOKUP formula was returning #N/A errors, instead of the correct results. The product numbers looked the same, but Excel didn't match them in the lookup.
Or, scroll down, to see how I fixed the formula.
The problem: After checking all the usual suspects, I found that the codes in the lookup table had hidden characters at the start and end. Maybe the data was copied from a file in a different language.
The fix: In cells E6 and F6, the LEFT and RIGHT functions return those hidden characters. Then, combine those with the product number in the VLOOKUP formula in cell E2, to get the product name:
=VLOOKUP(E6 & D2 & F6, $A$2:$B$6,2,0)
To see more detail on the problem and fix, download the sample file.
Thanks to Mohit Kejriwal for sending this question.
Here are a couple of Excel articles that you might find useful or interesting.
Charts - Jon Peltier shows how to work with blank cells, or #N/A cells in Excel charts. There are details for older versions of Excel, and the new setting in Excel 2016. (Level - Intermediate)
Dashboards - Closing Thursday, Feb. 15th - There's only a little time left to sign up for Mynda Treacy's highly-rated dashboard course. Click here to see details for the Excel Dashboard Course or the Power BI Course. (Level - Intermediate/Advanced)
Odd Facts - On the Data Rails blog, they posted "6 Cool Facts About Excel", but I can't find any proof of the 2nd (names) or 3rd (toolbars) items. Mike Alexander also mentioned those facts in an old post, and there's some discussion in the comments, but no links to a source. (Level - All)
It's a sure sign that winter has gone on too long, when you start to see faces in a bowl of Habitant Pea Soup! I put in a few dashes of hot sauce, and suddenly it looked like a Picasso painting. We enjoy the canned version of this French-Canadian soup, but Aube Giroux shares her mother's recipe -- she even grew special peas for it!
That's it for this week! If you have any comments or questions, send me an email.
NOTE: For the online version, paste this URL into your web browser: http://www.contextures.com/newsletter/excelnews2018/20180213ctx.html
I'll also post any article updates or corrections there.
ddalgleish @ contextures.com
Last updated: May 4, 2018 3:29 PM