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

Options

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

Options

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

• ✭✭✭✭✭✭
Options

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)