Some type of look up formula needed

Micah Turner
Micah Turner ✭✭✭✭
in Formulas and Functions

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?

Screenshot 2022-05-23 192222.jpg
Screenshot 2022-05-23 192259.jpg


· Share on FacebookShare on Twitter

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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


    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!