Contextures

Contextures News

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.

Tricky Price Lookups

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!

get correct price for invoice date

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])))

Learn more about INDEX and COUNTIFS on my website.

Personal Macros

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.

add personal macro to excel ribbon

Excel Articles

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)

Also see: My Excel Products || Excel Events || Weekly Excel Humour

Spring Flowers

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!).

weekly photo

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: http://www.contextures.com/newsletter/excelnews2017/20170425ctx.html

Debra
ddalgleish @ contextures.com

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: July 21, 2017 9:39 AM