Thanks Dave for your suggestions. It turns I had made a sheet level
name, so I think it was using the range on the current sheet to lookup
in. I was thinking Sheet1!$A$1 was sheet level name.

I like your way of referring to the range so I can use constants for
macro book name and lookup range name. I was doing something like:
Set rngRescodeLookup = Range("prep_labor_actuals.xls!RES_CODE_LOOKUP")

thanks,
Woody