Thread: Multiple Lookup for Split Agent Names

In column A of datasheet, I have a list of split agent codes. Each split code represents a split between 2-6 individuals per split ID.

For each split code, I am trying to lookup each agent ID assigned to the split code in question and their split %. There can be as many 6 agent IDs assigned to each split code.

I have attached a small sample, which includes the lookup table and data table, which better describes how the table is setup and expected results.

Try this in B4, with CTRL+SHIFT+ENTER, rather than just ENTER

=IFERROR(INDEX('Data Table'!$C:$F,SMALL(IF('Data Table'!$A$2:$A$17=$A4,ROW('Data Table'!$A$2:$A$17)),INT((COLUMNS($B4:B4)-1)/2)+1),IF(MOD(COLUMNS($B4:B4),2),1,4)),"")

Then copy across & down.