adjusting dynamic range formula

Hi,

I'm using a formula someone else created for me and I can’t modify it without creating a problem in my file. I'm confident the formula is correct, the problem is me. Here is the formula I am struggling with, it is found on the "red" tab A column.

=IF(ISERROR(INDEX('Master list'!$A$1:$C$99,SMALL(IF('Master list'!$B$1:$B$99="red",ROW('Master list'!$B$1:$B$99)),ROW(1:1)),2)),"",INDEX('Master list'!$A$1:$C$99,SMALL(IF('Master list'!$B$1:$B$99="red",ROW('Master list'!$B$1:$B$99)),ROW(1:1)),1))

I'm trying to dynamically update color team lists from a master list. Attached is a sample file.

The formula only references to row 99 in the master list, but I need it to go to 5,000. So if I adjust the formula, for example, $c$99 to $c$5000, A2 changes from "Thompson, Jewel" to "Staff Member", which is wrong.

I know it is because I really don't understand what the formula is doing - though I would really love to understand it.

I would really appreciate help in adjusting the formula correctly and helping me understand what is going on.

Thanks

Kara

This worked for me


=IFERROR(INDEX('Master list'!$A$1:$C$99,SMALL(IF('Master list'!$B$1:$B$99="red",ROW('Master list'!$B$1:$B$99)),ROW(1:1)),1),"")

array-entered of course

You are a wonder. It worked and I could adjust it. Thank you so much.

Kara