Index, Match, Collect, date range

Victoria HB

I have 2 sheets: 1) contains the study information 2) cost tracker that pulls the Speedtype#, price, etc. from #1

worksheet #1

Worksheet #2

in Worksheet #2, which is our cost tracker, when the IRB# is entered, the information gets pulled from Worksheet #1, including the price rate (if they select the correct type of service). Since the ST# can change yearly, I would like a formula that would also bring the most recent/active speedtype#. In other words, if the "completion date" is between ST# start date and end date (some are blank as the ST# is still active). For example for 18-0479, completion date 10/5/23, ST# should be 63512345.

I was using, "=IFERROR(INDEX({worksheet 1 IRB list ST#}, MATCH([IRB#/Study Name]@row, {worksheet 2 IRB list IRB#}, 0)), ""), but it's pulling 62654321. It should be 63512345.

Is there a formula to add, if completion date is > ST# start date, then pull up that corresponding Study ST#? I've tried , "=IF([Completion Date]@row > {worksheet 1 IRB ST# Start Date}, IFERROR(INDEX(COLLECT({worksheet 1 IRB list Multiple Range}, {worksheet 1 IRB list ST#}, [Study ST#]@row, 0)))), but it's giving me circular referencing error.

There will be multiple entries of the same IRB#/Study Name, but with updated Study ST# (or Speedtype#) which can change yearly. Thank you for your help. I'm totally baffled.

Philip Robbins

Hi @Victoria HB,

If I've understood you correctly, I would go for the following:

=index(collect({Worksheet 1 Speedtype #},{Worksheet 1 IRB#},[IRB#/Study Name]@row,{Worksheet 1 ST# End Date},""),1)

This assumes you maintain your start and end dates properly in worksheet 1, as it will pull back the first ST# it finds that matches the IRB# and doesn't have an end date (i.e. is current). There are other ways to do this, but this is likely to be the simplest.

If this doesn't work the way you want it to, let me know why and I'll post another suggestion.