Index/Match

Susan van Niekerk

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

Paul McGuinness

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

Hi

I get a no match error

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

Genevieve P.

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

Susan van Niekerk

Genevieve P.

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