Need help with Formula

Hello

I am not an expert in automation, So need your assistance in mapping one data. I have requirement wherein in one Smartsheet I have values in 3 columns and on the other Smartsheet I have all the values present 3 columns in Sheet 1 is present in a single column. I need put a function in such way that the formula first matches the value of Col 1 with values present in column of Sheet 2 if not matching then it will check in second column of Sheet 1 matches with sheet2 and so on. When any value matched in any one column in sheet 1 with sheet 2 the corresponding value present in the next column of sheet 2 will be picked and populated in Sheet1.

Please note:- Out of three columns at a time 1 column value will be matched with the value present in Sheet 2 for example if Value of cell in Col A matches with the cell value of sheet 2 then the values of other 2 will not be matching.

Thanks & regards

Anil Raghav

Answers

  • Hi Anil,

    You need to INDEX/MATCH your columns within an IFERROR function.

    Here is a screenshot of what I made:

    Let's suppose blue columns are Sheet1 and green ones are Sheet2.

    In your formula column, try this:

    =IFERROR(IFERROR(IFERROR(INDEX([Sheet2 index]:[Sheet2 index], MATCH([Column1]@row, [Sheet2 match]:[Sheet2 match], 0)), INDEX([Sheet2 index]:[Sheet2 index], MATCH([Column2]@row, [Sheet2 match]:[Sheet2 match], 0))), INDEX([Sheet2 index]:[Sheet2 index], MATCH([Column3]@row, [Sheet2 match]:[Sheet2 match], 0))), "NA")

    Is it what you are looking for?

    Mathieu | Workflow Consultant

    info@evolytion.com

  • Hello Champ,

    You made my day absolutely this is what I was looking for, Thanks a ton you made my day. You saved my lot of time.

    A big thank you appreciate your support.


    Best Regards

    Anil Raghav

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!