Cross Reference Sheet Formula Help

Jennifer Henry

I am needing help on a formula. I am trying to pull a clinical placement status from one sheet over to another but only if the hospital name and unit department matches. I also need to match the date to the cross reference sheet over 16+ columns that could have that date match.

I am currently using

=IF(HAS({Day of week range}, Date@row), INDEX(COLLECT({Clinical Placement Tool Status}, {Hospital Site Name}, [Hospital Name]@row, {Unit Name}, [Unit Name]@row), 0))

But this is pulling the date over and not looking to see if the hospital and unit matches.

Above is the sheet I am trying to pull the clinical placement status to but if Hospital Name, Unit, and Date matches an entry in the below sheet

The date in my first sheet needs to match one of the dates between 1st day of week date through 3rd day of week 16th week column so a range of dates

kioshi43

If I'm understanding your need correctly, would it help to incorporate the {Day of week range} into your INDEX COLLECT as a part of the criteria and then using an IFERROR if there is no match?

=IFERROR(INDEX(COLLECT({Clinical Placement Tool Status}, {Hospital Site Name},[Hospital Name]@row, {Unit Name}, [Unit Name]@row, {Day of week range}, Date@row), 1), "No Match")

Jennifer Henry

I tried but keep getting incorrect argument when I try to place the date range cells in the same brackets with the index collect formula

Aaron Saunders

Have you tried using an embedded OR function for the three date columns you are using as a range? Instead of trying to reference the three columns as a single range in the formula split them in to discrete column references using the OR as the conditional match so that if it matches one of the three it is fine. So, use = start of formula that you have, OR({rangeone}, [date]@row, {rangetwo}, [date]@row, {rangethree}, [date]@row),1), "No Match") and hopefully that works? You can also try splitting the formula into its discrete parts to see which part is failing.