=if(L8="","",if(L8<1,"",vlookup(L8,Command,2)))

Omitting the range lookup, as above, requires the table array: Command to be
sorted by the 1st col (lookup col) in ascending order. That's why you hit the
error for the "ACM" since Command wasn't sorted (as per post).

To avoid ambiguity in this kind of instance,
try instead an exact* match for the vlookup:
=IF(L8="","",IF(L8<1,"",VLOOKUP(L8,Command,2,0)))

*Use zero (or FALSE) as the 4th param (range lookup)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jimar" wrote:
I have a named ranged that has section numbers or letters in the first column
and Command Names in the second column ie
1 = Command 1
2 = Command 1
BNRP = Command 3
ACM = Command 4
I am using a formula that when the user types a member of staffs payroll
number their relevant section appears in one column ie 1,2,BNRP or ACM and
the corresponding command name is displayed in another column. This works
perfectly for all sections with numbers or letters except for section ACM
when N/A is displayed in the cell. If the A is removed from the section name
and just CM is left the formula works. Put the A back in and the formula
stops working. The formula I'm using is
=if(L8="","",if(L8<1,"",vlookup(L8,Command,2)))
Any help would be appreciated.
Thanks