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

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

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

Try

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


--
HTH

Bob Phillips

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

"jimar" wrote in message
...
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

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

=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

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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

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)

"Max" wrote in message
...
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

"Bob Phillips" wrote:
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...
Simply amazing, your spot on reading / prediction <g !

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!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

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)

"Max" wrote in message
...
"Bob Phillips" wrote:
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...
Simply amazing, your spot on reading / prediction <g !

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!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

"Bob Phillips" wrote:
Should be good, the quarters were a bunch of petulant affairs, but the semis
have been fine.

Agreed !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---