Formula not working when letter A is used in a named range

[Guest]

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

 

[Bob Phillips]

Try

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


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

=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)

 

[Guest]

OT: Bob, think you might have missed my follow up in:
http://tinyurl.com/mpclp
Your pick (Italy) is still well & truly on track .. <g>

---

 

[Bob Phillips]

Max,

If you recall, Italy was my original pick, and then I tipped France before
the Brasil game, so not bad eh? After the quarters and semis, it looks like
Italy to me, but if Zidane can turn it on again, if Henry can stop pouting
and run at the Italian defence, in Viera toughs it in midfield, France could
do it, but it looks like too many Ifs to me. Forza Italia!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Max,
If you recall, Italy was my original pick, and then I tipped France before
the Brasil game, so not bad eh?

Like I said in the follow up there...

After the quarters and semis, it looks like
Italy to me, but if Zidane can turn it on again, if Henry can stop pouting
and run at the Italian defence, if Viera toughs it in midfield, France could
do it, but it looks like too many Ifs to me. Forza Italia!

Shaping up to be a close, tight, and exciting game. Anyway, going by the
odds-makers here, Italy is the favourite to lift the World Cup, at 1.73 vs
1.95 (France). cheers!

---

 

[Bob Phillips]

Should be good, the quarters were a bunch of petulant affairs, but the semis
have been fine.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Should be good, the quarters were a bunch of petulant affairs, but the semis
have been fine.

Agreed !

---