I am trying to reference the first second occurrence of a name to index numerical values.

Question

I am attempting to index a column that contains unique numerical values (B2:B100) some of which are blank, by comparing one name (I4 - single cell reference) to a list of names (C2:C100). There are duplicates names in column (C2:C100) that have unique values in B2:B100. I have tried with no avail to reference the 1st, 2nd occurrence with the INDEX, SMALL and MATCH functions. The formula just returns the first then second cell in column B (B2 then B3)... but not the correct B column value corresponding to the indexed name in column C...
 
Here is the current formula:
=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX(Alaska!$B$2:$B$100,SMALL(IF(COUNTIF(Alaska!$C$2:$C$100,I4),MATCH(ROW(Alaska!$C$2:$C$100),ROW(Alaska!$C$2:$C$100)),""),ROWS($A$1:A1)),0),"X")), 1, 1)
Of note - column C2:C100 is generated from a drop down menu in a separate tab. In the above formula I4 is the single cell reference. This value is generated by an index-match array from the same C2:C100 list to filter the names that I need to get information for. What I tried to do is INDEX (unique numbers), SMALL(IF(COUNTIF(one name occurs once or more than once in a table of names...which it has to because the names are generated from the same column of data),MATCH(location of duplicates), ROWS(generate nth occurence), enter "X" if there is an error.
Thank you for the assistance.

{Stephen,G} · Accepted Answer

Hello Alaska,
It was great working with you on your sheet. We went a little bit of a combination of what you already had and some other stuff to get this one for you. Here is the formula we came up with and you can modify slightly for each different sheet.
=QUERY(ArrayFormula(IF(COUNTIF({ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX(Alaska!$C$2:$C$104,SMALL(IF(COUNTIFS($A$2,Alaska!$I$2:$I$104,$A$3,Alaska!$A$2:$A$104),MATCH(ROW(Alaska!$C$2:$C$104),ROW(Alaska!$C$2:$C$104)),""),ROWS($A$1:A1)),COLUMNS($A$1:A1)),"")), 1, 1);ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX(Alaska!$C$2:$C$104,SMALL(IF(COUNTIFS($A$2,Alaska!$I$2:$I$104,$A$3,Alaska!$A$2:$A$104),MATCH(ROW(Alaska!$C$2:$C$104),ROW(Alaska!$C$2:$C$104)),""),ROWS($A$1:A2)),COLUMNS($A$1:A2)),"")), 1, 1);ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX(Alaska!$C$2:$C$104,SMALL(IF(COUNTIFS($A$2,Alaska!$I$2:$I$104,$A$3,Alaska!$A$2:$A$104),MATCH(ROW(Alaska!$C$2:$C$104),ROW(Alaska!$C$2:$C$104)),""),ROWS($A$1:A3)),COLUMNS($A$1:A3)),"")), 1, 1);ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX(Alaska!$C$2:$C$104,SMALL(IF(COUNTIFS($A$2,Alaska!$I$2:$I$104,$A$3,Alaska!$A$2:$A$104),MATCH(ROW(Alaska!$C$2:$C$104),ROW(Alaska!$C$2:$C$104)),""),ROWS($A$1:A4)),COLUMNS($A$1:A4)),"")), 1, 1);ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX(Alaska!$C$2:$C$104,SMALL(IF(COUNTIFS($A$2,Alaska!$I$2:$I$104,$A$3,Alaska!$A$2:$A$104),MATCH(ROW(Alaska!$C$2:$C$104),ROW(Alaska!$C$2:$C$104)),""),ROWS($A$1:A5)),COLUMNS($A$1:A5)),"")), 1, 1);ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX(Alaska!$C$2:$C$104,SMALL(IF(COUNTIFS($A$2,Alaska!$I$2:$I$104,$A$3,Alaska!$A$2:$A$104),MATCH(ROW(Alaska!$C$2:$C$104),ROW(Alaska!$C$2:$C$104)),""),ROWS($A$1:A6)),COLUMNS($A$1:A6)),"")), 1, 1);ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX(Alaska!$C$2:$C$104,SMALL(IF(COUNTIFS($A$2,Alaska!$I$2:$I$104,$A$3,Alaska!$A$2:$A$104),MATCH(ROW(Alaska!$C$2:$C$104),ROW(Alaska!$C$2:$C$104)),""),ROWS($A$1:A7)),COLUMNS($A$1:A7)),"")), 1, 1)},Alaska!C2:C)>0,Alaska!B2:C,"")),"select * where Col2 is not null",0)
It looks big but most of that is building the literal array to get the list to countif against.
Stephen