Some type of look up formula needed

Micah Turner

Hello Experts!

I have a sheet that is compiling a lot of project information for P&L purposes. I need to find a formula that will enter the Project Manager for a Job number (each time it appears). I think the best place to pull that from is my project intake sheet. Please see below screenshots. Can someone recommend a formula to accomplish this?

Kelly Moore

Hey @Micah Turner

I would recommend an Index/Match for the lookup. For this specific instance, the formula below should work

=INDEX({Project Intake Sheet Project Manager column}, MATCH([Job Number]@row, {Project Intake Sheet Job Number column},0))

When referring to another sheet (cross sheet reference), once must build each reference physically using the formula window - one cannot simply copy paste this formula into your sheet. Your ranges will appear different - that is to be expected. I named them so you can see exactly what the range is.

Will this work for you?

Kelly

If interested, this article provides information on cross sheet references

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

Kelly Moore

Hey @Micah Turner

I would recommend an Index/Match for the lookup. For this specific instance, the formula below should work

=INDEX({Project Intake Sheet Project Manager column}, MATCH([Job Number]@row, {Project Intake Sheet Job Number column},0))

When referring to another sheet (cross sheet reference), once must build each reference physically using the formula window - one cannot simply copy paste this formula into your sheet. Your ranges will appear different - that is to be expected. I named them so you can see exactly what the range is.

Will this work for you?

Kelly

If interested, this article provides information on cross sheet references

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

Micah Turner

That worked. Thank you!