Need help with Formula

anilkrag1984

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

Mathieu PERSICO

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?

anilkrag1984

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