List from Join/Collect to return another values from another sheet

Morena
Morena ✭✭✭✭
edited 07/17/23 in Formulas and Functions

I have a join(Collect) list from another sheet in the cell. Now based on that list i want to use that join(collect) list to give me another join(Collect) list based on another sheet.

For example.

Sheet A

image.png

I pulled a list join(collection) list into Current Truck Status/s column. In this case, its two values Return - Uganda side & Songwe (R) - waiting for clearance. Now I want every item listed in the Current Truck Status/s column to return a list of Major Status/s based on Sheet B.


Sheet B

image.png

In this case I would expect a list of Available for imports allocations & Available for imports allocation in the Major Status/s column in Sheet A.

Please note Sheet B has items of 114, just cut them to 33 as you can see above.

I have used-

=IF(HAS([Current Truck Status/s]@row; "Songwe (R) - waiting for clearance"); INDEX({Major Status Mapping Range_Major}; MATCH("Songwe (R) - waiting for clearance"; {Major Status Mapping Range_Sub}; 0)) + CHAR(10))


This works but has limitations, where i couldn't add all items since they are 114.


Any suggestion on how else i can do this?

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!