Cross reference match formula

I am looking to cross reference Sheet A to pull in Time slots from Sheet B.

Criteria I need to have matching from Sheet A to pull from Sheet B to determine the slot number or multiple numbers.

Start Time
End Time

There could be multiple Slot Numbers so it would need to be a multi option that I could use later to separate in a report (time slots available).

Sheet A: Need Slot Numbers

Sheet B:

Find more posts tagged with

Formulas

Join

collect

INDEX

MATCH

Accepted answers

jmyzk_cloudsmart_jp

Hi @AngB789

I hope I understand your needs correctly. It sounds like you want to match the Start and End times in Sheet A with the corresponding time slots in Sheet B and pull multiple Slot Numbers into a single cell or a multi-select dropdown format.

To make this comparison more manageable, I added helper columns [S] and [E] to convert the Start Time and End Time into numerical values. This allows for easier filtering of the relevant slots.

https://app.smartsheet.com/b/publish?EQBCT=9c5c9e6b158b4ee9b9a950d516a5c642

[Slot Number] =JOIN(COLLECT({Sheet B: Time Slots : Slot Number}, {Sheet B: Time Slots : S}, >=S@row, {Sheet B: Time Slots : E}, <=E@row), CHAR(10))

[S] =VALUE(LEFT(TIME([Start Time]@row, 1), 2)) + VALUE(RIGHT(TIME([Start Time]@row, 1), 2)) / 60
[E] =VALUE(LEFT(TIME([End Time]@row, 1), 2)) + VALUE(RIGHT(TIME([End Time]@row, 1), 2)) / 60

To simplify time-based comparisons, we can use the TIME("hh:mm AM/PM",1) function, which converts times into a 24-hour format as a numerical value.

EXAMPLE: TIME("1:30 PM",1) → 13.50

https://app.smartsheet.com/b/publish?EQBCT=b1bf5a1e8674455eb4134844ee0baab7

All comments

jmyzk_cloudsmart_jp

Hi @AngB789

https://app.smartsheet.com/b/publish?EQBCT=9c5c9e6b158b4ee9b9a950d516a5c642

[Slot Number] =JOIN(COLLECT({Sheet B: Time Slots : Slot Number}, {Sheet B: Time Slots : S}, >=S@row, {Sheet B: Time Slots : E}, <=E@row), CHAR(10))

[S] =VALUE(LEFT(TIME([Start Time]@row, 1), 2)) + VALUE(RIGHT(TIME([Start Time]@row, 1), 2)) / 60
[E] =VALUE(LEFT(TIME([End Time]@row, 1), 2)) + VALUE(RIGHT(TIME([End Time]@row, 1), 2)) / 60

To simplify time-based comparisons, we can use the TIME("hh:mm AM/PM",1) function, which converts times into a 24-hour format as a numerical value.

EXAMPLE: TIME("1:30 PM",1) → 13.50

https://app.smartsheet.com/b/publish?EQBCT=b1bf5a1e8674455eb4134844ee0baab7

AngB789

@jmyzk_cloudsmart_jp that was very helpful!

jmyzk_cloudsmart_jp

Happy to help!😁

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.