Index and Match Formula with IF statements

Shannon Alvarez

I think I need an Index and Match formula, but cannot seem to find the correct syntax.

Sheet 1: Intake Sheet

I have an intake sheet that tracks an Intern's "program" and the semester it was completed. It is intended to track the intern's complete record. The sheet tracks this information over a potential three (3) semesters. Since an intern's start and stop times vary, each semester and program columns have multiple options to choose.

Example:

[Intern ID]	[Program1]	[Semester1]	[Program2]	[Semester2]
INT-0001	Internship I	Fall 2024	Internship II	Spring 2025
INT-0002	Internship II	Fall 2024
INT-0003	Internship I	Spring 2025	Internship II	Fall 2025

Sheet 2: Semester Placement Sheet

This sheet is tracking the individual placements for the interns in a particular semester. I need a formula that will look at each [Semester] column from the intake sheet for a particular intern AND semester and return the value from the corresponding [Program] column.

Example of Desired Outcome on the Spring 2025 Placement Sheet

[Intern ID]	[Program]
INT-0001	Internship II
INT-0002	No Match
INT-0003	Internship 1

Thanks in advance to the formula gurus!

Paul Newcome

You would use something along the lines of:

=IFERROR(IFERROR(IFERROR(INDEX(COLLECT({Program 1}, {ID}, @cell = [Intern ID]@row, {Semester 1}, @cell = "Spring 2025"), 1), INDEX(COLLECT({Program 2}, {ID}, @cell = [Intern ID]@row, {Semester 2}, @cell = "Spring 2025"), 1)), INDEX(COLLECT({Program 3}, {ID}, @cell = [Intern ID]@row , {Semester 3}, @cell = "Spring 2025"), 1)), "")