Make Formula SHORTER

[TiggerToo]

I need this formula shorter and simpler.

=if(c6=11,"",if(c6=0,"",vlookup(c6,dutycode!$a$4:$d$14,4,)))

column/row c6 has the code 11,
but if a "0" is put in place of 11, "#N/A" will appear. So I added, if c6=0,"" will make "#N/A" disappear.
Is there an easier way if c6 is blank or a "0" is place in c6.
I saw a posting someplace like =if(a1=11,a1=0,e4) I think it is an IF(or( type statement.

 

[Guest]

You are right;

=if(OR(c6=11,c6=0),"",if(c6=0,"",vlookup(c6,dutycode!$a$4:$d$14,4,)))

Thats all
suat

 

[Aladin Akyurek]

On 2002-03-01 17:29, TiggerToo wrote:
I need this formula shorter and simpler.

=if(c6=11,"",if(c6=0,"",vlookup(c6,dutycode!$a$4:$d$14,4,)))

column/row c6 has the code 11,
but if a "0" is put in place of 11, "#N/A" will appear. So I added, if c6=0,"" will make "#N/A" disappear.
Is there an easier way if c6 is blank or a "0" is place in c6.
I saw a posting someplace like =if(a1=11,a1=0,e4) I think it is an IF(or( type statement.

It seems to me that you want to avoid #N/A as result when your lookup-value is not vailable in your lookup table. It's not very clear which match-type (TRUE or FALSE) your VLOOKUP formula needs.

However, try:

=IF(COUNTIF($A$4:$A$14,C6),VLOOKUP(C6,dutycode!$A$4:$D$14,4,0),"")

This does an exact match (0 means FALSE) and returns a blank when the lookup-value is not in the lookup-table.

 

[TiggerToo]

On 2002-03-01 22:57, Aladin Akyurek wrote:

On 2002-03-01 17:29, TiggerToo wrote:
I need this formula shorter and simpler.

=if(c6=11,"",if(c6=0,"",vlookup(c6,dutycode!$a$4:$d$14,4,)))

column/row c6 has the code 11,
but if a "0" is put in place of 11, "#N/A" will appear. So I added, if c6=0,"" will make "#N/A" disappear.
Is there an easier way if c6 is blank or a "0" is place in c6.
I saw a posting someplace like =if(a1=11,a1=0,e4) I think it is an IF(or( type statement.

It seems to me that you want to avoid #N/A as result when your lookup-value is not vailable in your lookup table. It's not very clear which match-type (TRUE or FALSE) your VLOOKUP formula needs.

However, try:

=IF(COUNTIF($A$4:$A$14,C6),VLOOKUP(C6,dutycode!$A$4:$D$14,4,0),"")

This does an exact match (0 means FALSE) and returns a blank when the lookup-value is not in the lookup-table.

Thanks for the help.