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
Best Answer
-
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
Answers
-
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)
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!