Formula Help
Hello,
I am trying to figure out a formula that would function as such: Column B will pull a data value from another Smartsheet based on the matching data point in Column A.
Column A
123
Column B
(Based on what is in column A, will insert data value XYZ from Other Smartsheet)
Other Smartsheet
Column A
123
Column B
XYZ
Thanks for any insights you can provide!
Answers
-
Hey @shouse
The most effective way to do this will be through an Index Match pairing:
Put this formula is Column B of the 1st sheet
=INDEX({Reference Sheet 2 Column B}, MATCH([Column A], {Reference Sheet 2 Column A},0))
This will look at Sheet 2 & pull whatever value is in Column B on sheet 2, based on the value of Column A in Sheet 1.
Let me know if that clears it up or if you need any help referencing another sheet, etc!
-Jon Mark
-
Thank you! I tried this formula: =INDEX({Member Agent and Care Manager Range 1}), MATCH([MoreCare Member ID], {Member Agent and Care Manager Range 1},0))
I tried selecting different columns for the first and last reference, but it still says range 1 for both so it is not working. How can I select different ranges?
-
Ah yes, a couple adjustments it looks like you'll need to make:
=INDEX({Member Agent and Care Manager Range 1}), MATCH([MoreCare Member ID]@row, {Member Agent and Care Manager Range 2},0))
In order to get a second range - first make sure you're only selecting 1 column for each range.
Second - DELETE the current reference entirely, in that position in the formula, click the "Reference Another Sheet" option and select a new row (makes sure you DON'T just pick "Edit Reference"
That should fix it - let me know if it does or no!
-Jon Mark
-
Hm I tried this formula, but it did not fix it. Thank you!
-
Is there anything else I should try?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!