Make Formula SHORTER
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Make Formula SHORTER

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Macon, Georgia USA
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.







  2. #2
    Guest

    Default

    You are right;

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

    Thats all
    suat

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,751
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Macon, Georgia USA
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com