Formula Help

shouse

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!

Jon Mark H

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

shouse

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?

Jon Mark H

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

shouse

https://community.smartsheet.com/discussion/comment/325455#Comment_325455

Hm I tried this formula, but it did not fix it. Thank you!

shouse

https://community.smartsheet.com/discussion/comment/325455#Comment_325455

Is there anything else I should try?