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

Nug Neuman

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

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

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