What formula would I use to cross reference these sheets?

mperez

I have 2 sheets Events and Spaces. The spaces sheet has 4 columns labeled with the building that houses the spaces listed below it. The events sheet has a list of events and the corresponding spaces the event will use labeled as Booked Spaces. I would like to cross reference and have the value(s) returned for the building(s) that correspond to the booked spaces in the column labeled Site.

Thank you for your help!!

SSFeatures

Hi,

I just wrote this formula for you, and I think it should point you in the right direction.

=JOIN(COLLECT({Lot Reference}, {Center Reference}, CONTAINS(@cell, [Booked Spaces]@row)), ", ")

Let's say that in Sheet 2 you wanted to get all of the "Lots" from Sheet 1 for each of the booked spaces. This formula will collect all of the lots from Sheet 1 by scanning all of the centers in Sheet 1, and only collecting the lot if the name of the center is in "Booked Spaces". If there are multiple selected lots, then we add a comma and space to separate them.

I hope this helps!

SSFeatures