Index(Match()): Lookup a price of a product from another sheet but keep historical entries
Hi am am hoping to lookup a price of a product from another sheet based however keeping historical price changes.
Smartsheet A: We have production runs of product XYZ listed in a sheet A. Based on a concat we want to lookup a product price based on the MOST RECENT entry from Smartsheet B. However we want to keep Entry A of Product XYZ different than Entry B assuming the pricing has changed from Entry A to Entry B .
Smartsheet B is updated and time stamped when there is a pricing change of Product XYZ.
Index(match() works great if we are pulling pricing without date considerations. how could I incorporate dates into my Index(Match()?
Answers
-
This is a little tricky not having the Sheets to work with, but I think something like the below could work
=SUM(COLLECT({Price in Sheet B}, {Product List in Sheet B}, [Product Column]@row, {TimeStamp Column}, MAX(COLLECT({TimeStamp Column}, {Product List in Sheet B}, [Product Column]@row))))
Theoretically, the first MAX(COLLECT()) at the end pulls out the single highest time stamp for the product in question, that value is used in the first COLLECT() formula to pull out the price for the product you are searching for where the time stamp is equal to the max time stamp. The SUM is used so it will return a number instead of an error.
There are probably several ways to approach, perhaps with summary fields on your Sheet A either in the Sheet Summary section or as a parent row with all entries as children...
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
If the prices on Sheet B are updated in the same cell (meaning old prices are "overwritten" with the new prices), then I would suggest the INDEX/MATCH to pull the price and then a Move Row automation to push that row from Sheet A to a 3rd Sheet which would be your actual working sheet. The Move Row automation captures the static data, so update son Sheet B won't affect the previously pulled pricing.
@SoS | Dan Palenchar Have you tested your formula? When I have used a COLLECT inside of a COLLECT like that in the past, I have gotten a #NESTED CRITERIA error.
I believe you are also assuming that price updates are new entries so you could theoretically have multiple listings for "Product A" with different prices.
I would instead suggest a helper column that replicates the row numbers on Sheet B and then using something along the lines of...
=INDEX({Sheet B Price Column}, MAX(COLLECT({Sheet B Helper Column}, {Sheet B Product Column}, [Product Column]@row))
This also assumes that updates to the price list on Sheet B are new entries at the bottom of the sheet. If they are entries at the top of the sheet, then we would need to do a little tweaking either to the Helper Column or the INDEX formula.
-
@Paul Newcome I have not tested that and actually never tried a COLLECT in a COLLECT myself before, so I assume you're right and it won't work (also, good to know about that). And yes, I was assuming price updates as new rows (e.g., Entry A vs Entry B).
Your way looks better 👍️
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!