Contextures

Contextures News 20180206

Excel VLOOKUP Prices

February 6, 2018

Get product prices with VLOOKUP, 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.

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

Is It Sorted?

With Excel's data validation, you can show a drop down list of items in a cell. With formulas, you can also create "dependent" drop downs -- select a region, and see only the customers in that region.

customer list

My favourite way to set this up is based on a sorted list. This technique uses OFFSET, MATCH and COUNTIF to find the customers for the selected region.

=OFFSET(RegionStart, MATCH(B2,RegionColumn,0)-1, 1, COUNTIF(RegionColumn,B2),1)

For the OFFSET to work correctly, the lookup table MUST be sorted by the Region column. So, in the lookup table, a formula checks if region names are in A-Z order.

If not, the drop down shows a warning, "List Not Sorted", instead of customer names. You'll have to fix the list, before choosing a name. Do you have workbooks where you could use that type of warning?

Get the sample file, and see the complete instructions on my website.

customer list error message

Interactive Dashboards

There's only a little time left to join Mynda Treacy for a free one-hour webinar, and learn how to build interactive dashboards in Excel, with or without Power BI tools. The live webinars are only available until Thursday, Feb. 8th, so click here to register today.

Don't miss the discount - If you want to learn even more, Mynda offers full dashboard courses too. You can get 20% off if you register by Thursday, February 8th. Click here for the Excel Dashboard Course or the Power BI Course

Free Excel Dashboard Webinar

VLOOKUP Prices

See how to create a simple order form, and automatically get the correct price when a product name is entered. Go to my site, and watch the new video, to see how to do a price lookup with a VLOOKUP formula. It's near the top of that page.

vlookup price

Excel Articles

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

Excel Tips - Listen to John Michaloudis' podcast, to hear the best Excel tips from 2017. My pivot table tip is #8, at the 14:00 mark. The podcast player is at the top of the article, and you can download to listen offline. (Level - All)

Power BI Tool - If you find it painful to get Excel data into Power BI, and refresh it easily, take a look at the new XLPublish tool from Dick Moffat and David Hoppe. Scroll way down to the screen shot, and read the 2 paragraphs below that, for a quick idea of what the tool does. There's a free 30 day trial, then $5/month (developer), or $2 for users. (Level - Intermediate/Advanced)

Statistics - If you'd like to learn more about statistics, Crash Course has a great intro series on YouTube. It might help you get ready to use Excel's statistical functions. (Level - All)

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

On the Grid

Our grandson came to visit on the weekend, and challenged me to a game of Battleship. Long ago, I played the paper version, but this time we had a fancy plastic edition. My favourite part is the grid that the game board has, almost like Excel's, but with the letters and numbers reversed. And if you want to play Battleship in Excel, Andrew Engwirda has a free download (bottom of the page).

weekly photo

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/20180206ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
ddalgleish @ 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.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: May 4, 2018 3:28 PM