Formula needed to return value

Sandy O.

Hello! We are a small nonprofit and are trying to build a Smartsheet that allows volunteers to sign in, choose which outreach (activity) they do, and then based on their outreach choice, the formula would automatically assign a number of hours for that outreach (so that we can track volunteers number of hours worked.) I am assuming that the formula would reference another smartsheet table. So I have built a small Smartsheet with outreach names and the number of hours assigned to that outreach. I've tried a bunch of different formulas and still can't seem to get it to work. Would appreciate help! Thank you!

Screen Shot 2021-12-23 at 2.47.42 PM.png

Screen Shot 2021-12-23 at 2.48.23 PM.png

Kelly Moore

Hey @Sandy O.

The formula you need is an INDEX/MATCH. It has the syntax of INDEX(range where the data you need resides, MATCH(data in current sheet you're matching to, data range in source sheet that matches current sheet, 0)). The zero says your data is unsorted. You need that.

=INDEX({Outreach Type Sheet Value column}, MATCH([Outreach Type]@row, {Outreach Type Sheet Outreach Type column},0))

Because you are using a cross sheet references, you cannot simply copy paste this formula into your sheet. You must build each cross sheet reference (the references in the curly brackets). Your curly bracket names will be named differently than mine - yours will look like {Outreach Type Range 1}. Although not necessary to make the link work, a good practice is to rename these generically named ranges so that they reflect the actual column names you're using before you click the INSERT REFERENCE link. It helps you remember what the formula is doing with those names.

If you need a refresh on cross sheet references, you can find information here. And if you have any trouble at all, shout out and we'll work through it. Don't hesitate to ask questions. I'm happy to help.

Kelly

https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas