Excel VLOOKUP Prices
February 6, 2018
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
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.
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.
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
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.
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)
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).
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.
ddalgleish @ contextures.com
Last updated: February 8, 2018 10:53 AM