Tricky Price Lookups in Excel
April 25, 2017
Create a tricky lookup formula in Excel, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.
If there's just one price per product in an Excel lookup table, you could use VLOOKUP to get that price. But what if the price changes occasionally, and your pricing list has dates and prices? How can you find the correct price, based on the invoice date?
Someone asked that question on my Contextures blog last week, and I almost sprained my brain trying to come up with a formula! Here's the sample worksheet that I built, with the Jan. 10th invoice getting the correct price of $35 for product BBB.
I used a long formula, and you can see it below the screen shot. I'm sure there are other solutions, so you can download the sample workbook, and take a crack at solving it a different way. Let me know what you come up with!
Are you sitting down? Here's the formula that I used in the Amount column. It uses INDEX, SMALL, IF, ROW and COUNTIFS to find the matching product's latest price, on or before the invoice date. It's an array formula, so press Ctrl+Shift+Enter to enter it.
=INDEX(Products[Price], SMALL(IF(Products[Item]=[@Item], IF(Products[Pdate]<=[@Date], ROW(Products[Pdate]) - ROW(Products[[#Headers],[Pdate]]))), COUNTIFS(Products[Item],[@Item],Products[Pdate], "<="&[@Date])))
If you have macros in your Personal Workbook, you can add those to the Excel Ribbon or Quick Access Toolbar, so the macros are easy to use. Just right-click an empty space in the Ribbon, and click Customize the Ribbon. You can create a new tab, and put the macros there, or add a new group in an existing tab.
Then, at the top left, choose Macros from the drop down, and look for the items that start with "PERSONAL.XLSB". If you can't see the full name, point to a macro and the popup will show the full name. Select a macro, and click Add, to put it on the Ribbon.
Remember to click Export, to save your customizations, so you can recover them later, if necessary.
You can also create custom tabs that only appear when a specific workbook is opened. It's more work, but a nice feature to add in an Excel file.
Here are a couple of recent Excel articles that you might find useful.
Pivot Tables - On his Let's Excel blog, Don Tomoff shows how easy it is to format a pivot table with one click. He included one of my videos, so he must be very smart! ;-) (Level - All)
Excel Tables - Chandoo shows a problem with Excel Table formulas that refer to a cell in the row below. Have you run into that problem before? To fix it, I refer to the row above instead. (Level - Intermediate)
Power BI -- I've put together a Power BI Resources page on my website. There are links to helpful sites, and a couple of video playlists, to get you started. You can also download the resource list (in PDF format)
These lovely pink flowers appeared in the front garden last week, but I don't remember planting them! Maybe one of the neighbourhood squirrels buried them there. I think it's a Hyacinth (not the one from Keeping Up Appearances), but please let me know if I'm wrong (again!).
That's it for this week! If there are topics that you'd like to see covered in future emails,
please let me know.
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/20170425ctx.html
ddalgleish @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: July 21, 2017 9:39 AM