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()?

Find more posts tagged with

Finance

Formulas

Accepted answers

All comments

SoS | Dan Palenchar

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

Paul Newcome

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.

SoS | Dan Palenchar

@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 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.