Ignore that last post.

I was using the lastrow variable for the lastrow in both worksheets. That won't
work for you.

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 (and instead of using .autofill, I
like to fill the entire range at once), then you can do something like:

Dim LastRowP As Long 'Personnel sheet's last row
Dim LastRowS as Long 'SomeNameHere sheet's last row

With worksheets("Personnel")
lastrowP = .cells(.rows.count,"A").end(xlup).row
end with
With Worksheets("SomeNameHere") 'or With Activesheet
lastrowS = .cells(.rows.count,"A").end(xlup).row
.Range("K2:K" & lastrowS).Formula _
= "=IF($b2="""",""""," _
& "INDEX(Personnel!$B$1:$B$" & LastRowP & ",MATCH($b2," _
& "Personnel!$A$1:$A$" & LastRowP & ",0)))"
End With

Or using the entire column:

Dim LastRowS as long 'no need for the personnel last row in this!
With Worksheets("SomeNameHere") 'or With Activesheet
LastRowS = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("K2:K" & LastRowS).Formula _
= "=IF($b2="""",""""," _
& "INDEX(Personnel!$B:$B,MATCH($b2," _
& "Personnel!$A:$A,0)))"
End With

(I deleted the single cell formula. When I was changing my post, I realized
that that was just adding confusion.)

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