# Pick the first value in every row that has not been picked yet

In the example I am giving below, I would like the result in Column G to be what is in Column C, unless it already exists in Column G in rows above it:

These rows represent students' choices for topics (C is their 1st choice, D is their 2nd choice, etc.) and each topic needs to be unique. The first student gets their first choice, and then no one else can choose it. If another student in a successive row has chosen that as their first choice, then that student will have to be assigned their 2nd choice from Column D instead.

I have contemplated somehow using VLOOKUP or FILTER or even IF statements, but with 250 students/rows, that could get cumbersome.

• Besides the print screen, create a demo spreadsheet, share it with anyone with the link for view only and add it to the question. Feb 5, 2016 at 22:48

I think I figured it out myself. This works for 5 columns, but I could add more by extending the formula.

``````=if(isna(C12=vlookup(C12,\$G\$2:G11,1,false)),C12,if(isna(D12=vlookup(D12,\$G\$2:G11,1,false)),D12,if(isna(E12=vlookup(E12,\$G\$2:G11,1,false)),E12,if(isna(F12=vlookup(F12,\$G\$2:G11,1,false)),F12,""))))
``````

A shorter formula, which works the same for any number of columns, is

``````=array_constrain(filter(C2:F2, isna(vlookup(C2:F2, G\$1:G1, 1, false))), 1, 1)
``````

(The formula in G2 shown; it should be copied down the G column). Explanation:

• `isna(vlookup(C2:F2, G\$1:G1, 1, false))` is True for the values that have not appeared above in column G.
• `filter(C2:F2, ...)` filters the array C2:F2 by the above criterion
• `array_constrain(..., 1, 1)` returns the first element of the array.

Sample output (in G column):

``````+---+---+---+---+---+
| C | D | E | F | G |
+---+---+---+---+---+
| q | w | e | r | q |
| e | q | w | e | e |
| q | e | d | e | d |
| e | s | v | h | s |
| q | d | s | r | r |
+---+---+---+---+---+
``````