Index/Match

Options

Hi Brain Trust


I am after a formula that will assist me in pulling data from another sheet if the name columns match

So IF "assigned to" in the project Services Seating Plan matches the name

Project Services sheet "Name Column" I want it to return the Desk number to the Desk column in Desk # Column


Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Options

    Hi @Susan van Niekerk

    A index match formula should do that for you.

    This is how that would lay out but you will need to personalise the highlighted sheet references to reflect the two columns in the project services sheet.

    =Index({Desk # column project services sheet},Match([Assigned to]@row,{name column project services sheet},0))

    Hope that helps

    Thanks

    Paul

  • Susan van Niekerk
    Options

    Hi


    I get a no match error


    =INDEX({Project Service Desk #}, MATCH([Desk Number]@row, {Project Service Roster 2023 Range 2}, 0))

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @Susan van Niekerk

    Is it possible that the Desk Number in your current sheet is not listed in your {Range 2} column? Or that there's a slight difference in the way it's been input (e.g a period in a different place).

    To test and make sure your Desk Numbers are finding a match, I would use a COUNTIF formula to see if the Formula is able to read your data properly:

    =COUNTIF({Project Service Roster 2023 Range 2}, [Desk Number]@row)

    ^ If this is 0 then the current value isn't able to be found in the source sheet.

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 09/26/23
    Options

    Hey @Susan van Niekerk

    Thank you for this image, that helps a lot! The next thing to check is to ensure your ranges are the right way around.

    • The first one listed in the INDEX should be the one you want to bring back, so I believe in this case the Assigned To column?
    • The second one listed, in the MATCH function, should be the value you're matching on. Based on your recent images, I believe this is the column that has the same number as your Desk Number, in order to bring back the Assigned To person.

    =INDEX({Assigned To Column}, MATCH([Desk Number]@row, {Desk Number Column}, 0))

    Can you double check where each of these ranges are pointing, and make sure they're referencing the correct columns?

    Here's more information on Index(Match: Formula combinations for cross sheet references


    Let us know if we've misunderstood what it is you're looking to match on and return.

    Thanks!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!