Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

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
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Don't confuse shorter with better or more effiecient, often not the case. But here are 2 more options.

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


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


    I alos note you haven't used False as the Range_lookup argument, you may want to change that?

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Don't confuse shorter with better or more effiecient, often not the case. But here are 2 more options.

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


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


    I also note you haven't used False as the Range_lookup argument, you may want to change that?


Some videos you may like

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
  •