I think your formula is wrong.

I think you'd want something like:
=IF($B2="","",INDEX(Personnel!$B$1:$B$99,MATCH($B2 ,Personnel!$A$1:$A$99,0)))

(single cell to start the =if(), a single cell to match (B2, not B2:B8000) and
$b$2 is changed to $b2 in both spots.)

If you can pick out a column on the personnel worksheet that always has data in
it so you can use it to find the last row, then you can use something like:

Dim LastRow As Long
With Worksheets("SomeNameHere") 'or With Activesheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("K2").Formula _
= "=IF($b2="""",""""," _
& "INDEX(Personnel!$B$1:$B$" & LastRow & ",MATCH($b2," _
& "Personnel!$A$1:$A$" & LastRow & ",0)))"
End With

Personally, I'd just use the entire column:

With Worksheets("SomeNameHere") 'or With Activesheet
.Range("K2").Formula _
= "=IF($b$2="""",""""," _
& "INDEX(Personnel!$B:$B,MATCH($b$2," _
& "Personnel!$A:$A,0)))"
End With

And instead of using .autofill, I like to fill the entire range at once.

Dim LastRow As Long
With Worksheets("SomeNameHere") 'or With Activesheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("K2").Formula _
= "=IF($b2="""",""""," _
& "INDEX(Personnel!$B$1:$B$" & LastRow & ",MATCH($b2," _
& "Personnel!$A$1:$A$" & LastRow & ",0)))"
End With

Or using the entire column:

Dim LastRow As Long
With Worksheets("SomeNameHere") 'or With Activesheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("K2:K" & LastRow).Formula _
= "=IF($b2="""",""""," _
& "INDEX(Personnel!$B:$B,MATCH($b2," _
& "Personnel!$A:$A,0)))"
End With



Bud wrote:

Hello

I have the following matching formula that works fine and than another
statement that does a fill down using br to know when to stop the fill down.

Is there someway to modify the matching formula or to use a Lookup formula
that I can tell it what row to stop at.

I count the number of rows in a previous worksheet to know how many rows
there are but I don't know how to substitute that variable in place of that
8000 count I have.

Each week we run this report we could have differing number of rows. I just
represent it with a higher number because I don't know what else to do.

Cells(2, "k") =
"=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))"

Cells(2, "k").AutoFill Destination:=Range(Cells(2, "k"), Cells(br, "k"))

--

Dave Peterson