How can look for specific data in a column and match it on Sheet 2 (in SS)

Sheet 1

I want to put a formula in Sheet 1, column [PSPEC Product Requirement], that looks at [References]@row on Sheet 1, then matches that references on Sheet 2 [References].

Sheet 2

Then copies the content on same Row on Sheet 2, [PSPEC Product Requirement], and puts it in Sheet 1, [PSPEC Product Requirement]

I've tried this:

=INDEX({WS- RCV RTM

Reference Sheet Range 3}, MATCH(References@row, {WS- RCV RTM

Reference Sheet Range 3}, 1)), however it pulls the right column in Sheet two, but the last entry in column, which is not a match for Reference in Sheet 1.

Please help! Urgent. Thanks in advance.

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @dezertwarrior

    Your formula should be along the lines of:

    =INDEX({PSPEC Product Requirement}, MATCH(References@row, {References}, 0))

    Hope this helps, but let us know if you've any problems/questions!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 01/18/24

    Hi @dezertwarrior

    I hope you're well and safe!

    Your syntax is a little off. Here's the structure.

    =INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainstTheCell}, 0))

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Thanks Andree- So I used that formula, it came back with information, but returned the same matching data (RCV- PSPEC-534) I wanted to match from Sheet 1, to Sheet 2, which is the same.


    Sheet 1 with formula in column- [PSPEC Product Reference].

    I know it's returning data from Sheet 2, because when I change the reference in Sheet 1, to a unused reference on Sheet 1, It returns "No Match found". Does this make sense?


    This is what I am using:

    =INDEX({WS- RCV RTM Reference Sheet Range 1}, MATCH(References@row, {WS- RCV RTM Reference Sheet Range 1}, 0)), Returns . Really apprecaite the quick response.

  • Thanks Nick- It did not work. Should the columns I'm references be in a certain order, and should the be in the same order on each sheet?

    Thanks

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 01/18/24

    @dezertwarrior

    Happy to help!

    Strange!

    I'd be happy to take a quick look.

    Can you share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!