How do I return a value from a chart on another sheet using multiple values?

I have a Data Chart for wiring harnesses based on Year, Make, and Model of commercial Vehicle (it's quite extensive)

I have another chart that has incoming orders, where I am given the year, make, and model of the vehicle

I need to return the harness from the Data Chart formulaically. In excel, I am able to do this with Sumproduct

How would this be done in Smartsheet. Some harnesses are used over a multi year period, so the chart shows a min and max year applicable to that harness

Example below

Data Chart (the Value is used in a Vlookup formula to find that harness by value)

Order Sheet

My previous excel formula was

=VLOOKUP(SUMPRODUCT(--('Data Chart'!$C$2:$C$69=G2027),--('Data Chart'!$D$2:$D$69=H2027),--('Data Chart'!$E$2:$E$69<=I2027),--('Data Chart'!$F$2:$F$69>=I2027),'Data Chart'!$G$2:$G$69),'Data Chart'!$I$2:$J$12,2,FALSE)

SUMPRODUCT finds the match where Make = Make, Model = Model, and year is in the range (>min, <max), then returns the Value, which is used in the Vlookup

So a 2006 Mack LE would return the Value 6

I would remove the VLOOKUP portion and simply use the harness as a return value, but sum product only returns a number

Thanks in advance for the help

Find more posts tagged with

Accepted answers

Nug Neuman

Thanks Leibel S

worked great. had a few invalid values, but turns out some of the "Years" were text values. Retyped as numbers and everything worked.

much appreciated

All comments

Leibel S

@Nug Neuman

The below is the general concept. You would need to create your cross sheet references and update the names in the below formula.

=INDEX(COLLECT({VALUE RANGE},{MAKE RANGE},[Chassis Maker]@row, {MODEL RANGE},[Chassis Model]@row,{YEAR MIN RANGE},<[Chassis Year]@row, {YEAR MAX RANGE},>[Chassis Year]@row),1)

Nug Neuman

Thanks Leibel S

worked great. had a few invalid values, but turns out some of the "Years" were text values. Retyped as numbers and everything worked.

much appreciated

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.