Alternative for Nested IF formula

Page 1 of 5 123 ... LastLast
Results 1 to 10 of 41

Thread: Alternative for Nested IF formula

  1. #1
    New Member
    Join Date
    Aug 2017
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Alternative for Nested IF formula

     
    Hi Guys,
    Chose the right box to store Part A which has volume of 2.7

    Box name(Column A) Box Volume(Column B)
    b1 1
    b2 2
    b3 3

    Based on on above table i would use an if conditions which goes like this,
    =IF($F$1<=B2,A2,IF($F$1<=B3,A3,IF($F$1<=B4,A4)))

    is there any other alternative instead of using IF function. the above example contains 3 rows, however in reality i would need to apply IF func for 100+ rows. Please help.

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    6,496
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Alternative for Nested IF formula

    Use a lookup table.

    VLOOKUP or INDEX(...MATCH...)

    e.g.

    =VLOOKUP(Lookupvalue,Sheet2!A$2:B$4,2,0)
    Last edited by Special-K99; Aug 17th, 2017 at 09:03 AM.

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

    Default Re: Alternative for Nested IF formula

    Looks like...

    =LOOKUP(F1,B:B,A:A)

    for A:B seems to be sorted inascending order on column B.
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    Board Regular
    Join Date
    Aug 2017
    Posts
    342
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Alternative for Nested IF formula

    =IFERROR(INDEX(A2:A4,(MATCH(ROUND(F1+0.4,0),B2:B4,1))),"Don't fit!")

  5. #5
    New Member
    Join Date
    Aug 2017
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Alternative for Nested IF formula

    Quote Originally Posted by mart37 View Post
    =IFERROR(INDEX(A2:A4,(MATCH(ROUND(F1+0.4,0),B2:B4,1))),"Don't fit!")
    Thank you for the above response. Worked well! Appreciate it

  6. #6
    New Member
    Join Date
    Aug 2017
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Alternative for Nested IF formula

    It worked only incase if the value is an integer. however in case there is decimal it takes the 1st unit place value.

    Thank you for your response

  7. #7
    New Member
    Join Date
    Aug 2017
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Alternative for Nested IF formula

    Quote Originally Posted by mart37 View Post
    =IFERROR(INDEX(A2:A4,(MATCH(ROUND(F1+0.4,0),B2:B4,1))),"Don't fit!")
    Hi,
    Ideally if i use F1 value more than 3 it should show me "Don't fit", however in this case the result shows a A4 Cell.
    Please help.


    And may i know why have you used F1+0.4?

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,444
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Alternative for Nested IF formula

    How about post #3 ?
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    Board Regular
    Join Date
    Aug 2017
    Posts
    342
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Alternative for Nested IF formula

    depends how many decimals. 0.4999999999 have a beter result.
    Last edited by mart37; Aug 17th, 2017 at 10:30 AM.

  10. #10
    New Member
    Join Date
    Aug 2017
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Alternative for Nested IF formula

      
    Quote Originally Posted by Aladin Akyurek View Post
    How about post #3 ?
    Hi,
    it didn't work if the number is decimal. based on the above example the part should go to B3 however usung the formula you mentioned, it chose B2.

User Tag List

Tags for this Thread

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