Search Multi Column Array

[Timmer2005]

Hello, I am trying to search for data between two columns and have the data list in another. I can get it to work for one column but when I add the second column to the array I loose data. Here is the formula that is working for one column now:

=IFERROR(INDEX(Names!A:A,AGGREGATE(15,6,ROW(Roster[Student Name])/(Roster[System]={"System 8","System 9","System 10"}),ROWS(R$3:R3))),"")

But when I change the formula to the one below (difference in bold), I loose data:

=IFERROR(INDEX(Names!A:A,AGGREGATE(15,6,ROW(Roster[Student Name])/(Roster[[System]:[System 2]]={"System 8","System 9","System 10"}),ROWS(R$3:R3))),"")

I have also attached a document to aid in seeing what is happening. Thank you for the help.

[FlameRetired]

The formula
=IFERROR(INDEX(Names!A:A,AGGREGATE(15,6,ROW(Roster[Student Name])/(Roster[[System]:[System 2]]={"System 8","System 9","System 10"}),ROWS(R$3:R3))),"")
creates a 2 dimensional array with this part (Roster[[System]:[System 2]]={"System 8","System 9","System 10"})
(makes two columns)
It is not compatible with the one dimensional array part returned by ROW(Roster[Student Name]) (one column)

This is one way to reconcile that

Please Login or Register  to view this content.

It returns this at my end.

	S
1	Thursday
2	System G
3	Student 5
4	Student 10
5	Student 12
6	Student 13
7	Student 16
8	Student 17
9
10
11
12
13
14

If you would like to know more about multi-array formulas and re-dimensioning this is an excellent site to study. Careful. It can be addicting. LOL https://excelxor.com/

[Timmer2005]

Hi Dave,

Thank you so much. That is exactly what I was looking for. From my testing if I was only looking for one item (System 8) between the two columns [[system]:[system 2]] it works but when I was searching for multiple items (System 8, System 9) between the two columns it did not work. But your formula and explanation worked perfect. I now see all students who need to attend the system no matter which column it is listed in. And I will be sure to check out that website.

Tim

[FlameRetired]

You are welcome. Glad it was helpful.

Thank you for the feedback, added rep and marking your thread Solved.