I can't access the original files, so I don't know what your data looks like, but

Here's one way using a formula with the Match and Index functions (Vlookup is shorter but requires the lookup list to be sorted)

Rates.xls is a spreadsheet that has haircut rates (column C) for the 3 Stooges (column A). This would correspond to your "Archive" worksheet.

Formula.xls is a spreadsheet that will populate column C if you type a customer name in column A (only for rows 2 through 15, since the formulas in column C are only in those rows).

If there is no name in Column A, or the name in Column A is not one of the 3 Stooges, then the conditional formatting of C2:C15 hides the #N/A.

You could also populate column C with an Event Change macro, much more elegant.

-AndyM

I can't access the original files, but a lookup would have been better in the example than using index/match formulas together with conditional formatting

index/match is the lookup you use when the value you are returning is to the left of your lookup value (ie if you want to lookup a value in column C, but return a value from column A)

and so long as the lookup values don't have multiple results (eg where looking up MOE could return 1, 2, or 3) vlookup is perfectly acceptable (sorted or otherwise, at least that's my experience, just use the exact match option)

error values in your results can also be avoided by using conditional statements, eg

=if(iserror(vlookup(value, range, column, TRUE)),"",vlookup(value, range, column, TRUE))

if the lookup value isn't found it returns a blank, otherwise it returns the lookup value

that could be extended so that blank cells don't calculate, eg

=if(cell="","",if(iserror(vlookup(value, range, column, TRUE)),"",vlookup(value, range, column, TRUE)))

if your reference range is only small, use absolute referencing, or a named range