PLEASE HELP: Formula to Pull Information in Order (Example Attached)

Using the data list, I want to get the Student ID via the formula and then use a vlookup function to look up the student name, priority, and class (there will be conditional formatting later based on these vlookup values). The formula will be copy and pasted in every "ID" column... Below is the priority designation of who gets put first, second, third, etc. in the specific college that is listed.


DESIRED RESULT (Listed by Priority)


1. PRIORITY AND SENIOR
2. SENIOR
3. PRIORITY AND JUNIOR
4. JUNIOR
*SCHOOLS WITH MULTIPLE IN EACH CATEGORY WILL BE LISTED IN ALPHABETICAL ORDER (The data will be in alpha order)

Attached is an example with a desired result and the data.

In C6 try this Array* formula:

=IFERROR(INDEX($T$6:$T$23,SMALL(IF($W$6:$W$23=$B6,ROW($T$6:$T$23)-ROW($T$6)+1),MID(C$5,3,10)+0)),"")

copied down.

Then copy C6:C10 to G6, K6, O6.

Then do the Vlookups....

*Array formula require you to confirm with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula

NBVC said:

In C6 try this Array* formula:

=IFERROR(INDEX($T$6:$T$23,SMALL(IF($W$6:$W$23=$B6,ROW($T$6:$T$23)-ROW($T$6)+1),MID(C$5,3,10)+0)),"")

copied down.

Then copy C6:C10 to G6, K6, O6.

Then do the Vlookups....

*Array formula require you to confirm with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula

Click to expand...

Thank you so much for your help. This is almost correct, but it doesn't take into account the priority or class variables (Seniors first and priorities of that class first)... is there something else I can add to fix that?

Ok. Looks like we'll need to do it in steps.... using some helper columns in your data.

What I did is...

1. add helper column in Y6 for identifying priority numerically with formula: =IF(V6="Yes",IF(X6="Senior",1,3),IF(X6="Senior",2,4)) copied down
2. add helper column in Z6 for ranking the college by priority with formula: =W6&"_"&COUNTIFS($W$6:$W$23,W6,$Y$6:$Y$23,"<"&Y6)+COUNTIFS($W$6:W6,W6,Y$6:Y6,Y6) copied down
3. Now formula in results area, C6 is: =IFERROR(INDEX($T$6:$T$23,MATCH($B6&"_"&MID(C$5,3,10)+0,$Z$6:$Z$23,0)),"") copied down
4. Copy C6:C10 to G6, K6, O6.

See attached sample.

Does that work?

@ rosoet

Please do not quote entire posts unnecessarily. They clutter the thread and make it hard to read. Thx

Perfect. Thank you so much!

You're welcome.