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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!