Index Collect partially working

Kevin7859

I am trying to validate information using Index Collect function. It works when I look at the school's name, but when I add the 2nd criteria for the school district, I get the #Incorrect Argument Set error.

SCID=the ID number

SN is school Name in the criterion range

DN is the district name in the criterion range

=INDEX(COLLECT({SCID}, {SCMLSN}, [SCHOOL_NAME_LONG]@row, {SCMLDN}, [District_Name]@row), 1)

Doing this because we have same name schools in multiple school districts. Was getting an ID# conflict in returned results using index match originally, but it only gave me the 1st instance ID found and not subsequent iterations.

Jennifer Kurtz

Hi! I use an INDEX(COLLECT for this exact use case as well! :)

I would check that your ranges are set appropriately. That seems to be a common culprit with that formula error.

Good luck!

Paul Newcome

Have you double checked that your ranges are pulling from the same sheet and are the same size and shape? There are times where things will go a little weird when creating a cross sheet reference, and the reference will be the top left cell of the sheet. There are some timing issues and details that make this happen at times, and it happens without warning.

Paul Newcome

Happy to help. 👍️

Jennifer Kurtz

Hi! I use an INDEX(COLLECT for this exact use case as well! :)

I would check that your ranges are set appropriately. That seems to be a common culprit with that formula error.

Good luck!

Kevin7859

it seems that if there are multiple results then it throws the #Incorrect Argument Set error.

So with School name by itself, it works, but the minute I add the District filter it breaks.

What I need is a formula that will return the school # from one table when the name, district and ID # are used for the range and criteria. I tried using just the school 6 digit ID# which is different from the school # and it comes back with blank results. Is it doing this because the 6 digit # is a formula result?

Paul Newcome

Are you able to provide some screenshots for context? What is the formula populating the 6 digit #?

Kevin7859

the formula is

=JOIN([2Dig Dist #]@row:[4Dig MSID]@row, "")

The SCID by Name is the Index Collect formula.

I am having to use a formula to ensure the District # is always 2 digits and the School # is always 4 digits:

(2 Dig Dist #) =IFERROR(IF(LEN(DISTRICT@row) = 1, "0" + DISTRICT@row, IF(LEN(DISTRICT@row) = 2, "" + DISTRICT@row)), "")

(4Dig MSID) =IFERROR(IF(LEN(SCHOOL@row) = 1, "000" + SCHOOL@row, IF(LEN(SCHOOL@row) = 2, "00" + SCHOOL@row, IF(LEN(SCHOOL@row) = 3, "0" + SCHOOL@row, SCHOOL@row))), "")

I have a column with the district names as well (not shown).

Using the SCID (system assigned number not associated with any other item, this is our primary key) by Name to validate data across multiple sources, using another column to validate the SCID by the 6 digit MSID (Dist+MSID) and then comparing that column with the SCID by Name reference t ensure the 2 data sources have the same information, because sometimes school names and numbers change and we want to find those discrepancies for further review.

Paul Newcome

Have you double checked that your ranges are pulling from the same sheet and are the same size and shape? There are times where things will go a little weird when creating a cross sheet reference, and the reference will be the top left cell of the sheet. There are some timing issues and details that make this happen at times, and it happens without warning.

Kevin7859

That seemed to work with the District name, it was a bad reference. Had to go through the reference manager slowly to find the right one.

Thanks for all the help.

Paul Newcome

Happy to help. 👍️