Help with formula: return value from cell in same row if criteria is met

Alyson Harris
Alyson Harris ✭✭✭
edited 04/29/24 in Formulas and Functions

Hi, I am trying to figure out how to write a formula to return a value from the SAME ROW, when criteria is met. I can't figure out what I am doing wrong with index/match/collect - I am hoping someone can help! Most of the tips/tricks/Community answers I have seen are using columns not rows, so I am not sure how to do it. I index across sheets all the time, but this has me stumped.

Here is what I am trying to do: in a single row - if [Asset Type] contains X, return value from [X Link] @row; if [Asset Type] contains Y, return value from [Y Link] @ row…. and so on.

Asset Type is a multi-select drop down, so I will need to use a Contains function to find which asset type we are looking for, and bring in the appropriate link to that asset. Our links are in several columns based on the asset type.

We need to consolidate the links into a single column for an automation/integration we are working on, and with thousands of rows, we don't want to do it manually.

Can anyone help me figure this out?

THANK YOU in advance!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!