Returning cell based on 2 criteria in another sheet

maineL

Hi,

I'm back! I am trying to pull the course cell per semester based on a student's ID and also on the Term (fall or spring) columns.

This sheet is my "eligibility sheet" where I would like the course from my Active TAships sheet to go

Active TAships Sheet:

The formula I am using in the FA Acceptance (also will be in Sp Acceptance column) is: =INDEX({ActiveCourse}, MATCH([CUID (7 digits)]@row, ({ActiveID}), 0))

While this does populate on the Eligibility Sheet the FA Acceptance, it duplicates it for the SP Acceptance…I'm sure it has something to do with the Term field, but I cannot get anything but errors.

Any ideas? Thank you!

Lori

jmyzk_cloudsmart_jp

Hi @maineL

Thank you for an interesting real-world example.

Here is a solution using the JOIN(COLLECT()) functions.

Formula for Fall (FA Acceptance)

To retrieve courses for the fall semester, use this formula:

=JOIN(COLLECT({ActiveCourse}, {ActiveID}, VALUE(@cell) = VALUE([CUID (7 digits)]@row), {ActiveTerm}, "F"), CHAR(10))

Formula for Spring (SP Acceptance)

To retrieve courses for the spring semester, use this formula:

=JOIN(COLLECT({ActiveCourse}, {ActiveID}, VALUE(@cell) = VALUE([CUID (7 digits)]@row), {ActiveTerm}, "S"), CHAR(10))

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

Explanation of Each Part:

• {ActiveCourse}: This is the range containing the course names in the Active TAships sheet.
• {ActiveID}: This is the range containing CUIDs in the Active TAships sheet, matching the CUID in your eligibility sheet.
• VALUE(@cell) = VALUE([CUID (7 digits)]@row): This ensures that the CUID values in both sheets are treated as numeric values. This step prevents mismatches due to any formatting differences (e.g., leading zeros or text format).
• {ActiveTerm}: This range contains the term information ("F" for fall and "S" for spring) in the Active TAships sheet.
• "F" or "S": This specifies the term filter, pulling data for either fall ("F") or spring ("S").
• JOIN(..., CHAR(10)): Combines any matching courses into a single cell, with each course on a new line (using CHAR(10) to add line breaks).

Usage:

• FA Acceptance Column: Use the formula with "F" to pull only fall courses.
• SP Acceptance Column: Use the formula with "S" to pull only spring courses.

This approach should now give the correct, term-specific course list without duplicates. Let me know if there’s anything else I can assist with!

maineL

I also tried this formula: =MAX(COLLECT(Lookup File - {ActiveCourse}, {Lookup File - {ActiveID}, =[CUID (7 digits)]@row,{ActiveTerm},0) and get unparseable.

jmyzk_cloudsmart_jp

Hi @maineL

Thank you for an interesting real-world example.

Here is a solution using the JOIN(COLLECT()) functions.

Formula for Fall (FA Acceptance)

To retrieve courses for the fall semester, use this formula:

=JOIN(COLLECT({ActiveCourse}, {ActiveID}, VALUE(@cell) = VALUE([CUID (7 digits)]@row), {ActiveTerm}, "F"), CHAR(10))

Formula for Spring (SP Acceptance)

To retrieve courses for the spring semester, use this formula:

=JOIN(COLLECT({ActiveCourse}, {ActiveID}, VALUE(@cell) = VALUE([CUID (7 digits)]@row), {ActiveTerm}, "S"), CHAR(10))

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

Explanation of Each Part:

• {ActiveCourse}: This is the range containing the course names in the Active TAships sheet.
• {ActiveID}: This is the range containing CUIDs in the Active TAships sheet, matching the CUID in your eligibility sheet.
• VALUE(@cell) = VALUE([CUID (7 digits)]@row): This ensures that the CUID values in both sheets are treated as numeric values. This step prevents mismatches due to any formatting differences (e.g., leading zeros or text format).
• {ActiveTerm}: This range contains the term information ("F" for fall and "S" for spring) in the Active TAships sheet.
• "F" or "S": This specifies the term filter, pulling data for either fall ("F") or spring ("S").
• JOIN(..., CHAR(10)): Combines any matching courses into a single cell, with each course on a new line (using CHAR(10) to add line breaks).

Usage:

• FA Acceptance Column: Use the formula with "F" to pull only fall courses.
• SP Acceptance Column: Use the formula with "S" to pull only spring courses.

This approach should now give the correct, term-specific course list without duplicates. Let me know if there’s anything else I can assist with!

jmyzk_cloudsmart_jp

maineL

You are AMAZING!!!! Thank you so much! I've been pulling my hair out!!!! This worked perfectly! THANK YOU!!!!

jmyzk_cloudsmart_jp

Happy to help! 😁 @maineL