+ Reply to Thread
Results 1 to 7 of 7

Avoid using nested IF

  1. #1
    Registered User
    Join Date
    01-16-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    4

    Avoid using nested IF

    Hi to all

    What I want to do is avoid using nested ifs because it would take A LOT of work, and I think it would be pointless

    I have a spreadsheet with a table in H10:I14 which has:

    1 3
    5 8
    9 12
    15 20
    21 45

    And so on, for like 400 rows.

    Above that table in cell A2, B2 etc I want to put a formula which compares a number in A3, B3, etc up to AUF3 with those ranges and return 1 if it's within one of those ranges and nothing if it isn't.

    For example if in A3 I have the number 2, I should see "1" in A2 (because it's between the first range 1:3), but if there's a 4 in C3, there shouldn't be values in C2.

    I know I could use nested IFs (=IF(AND(A3>H10;A3<=I10);1;IF(AND(A3>H11;A3<=I11);1; and so on but I have A LOT of ranges and i think this could be solved easily, but I don't have the resources and experience to come up with a simpler solution

    What I want to do is compare all ranges at the same time with one cell, which is that A2, B2, C2, etc

    Thanks in advance
    Last edited by GPozzi; 02-28-2017 at 04:41 PM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Avoid using nested IF

    Isn't 4 also in the range 1:5?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    01-16-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    4

    Re: Avoid using nested IF

    Sorry, I'll correct it. I wanted to say 2 is between 1 to 3. The ranges are horizontal: 1 to 3, 5 to 8, 9 to 12, 15 to 20, 21 to 45 and so on
    Last edited by GPozzi; 02-28-2017 at 04:38 PM.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Avoid using nested IF

    Are you familiar with Excel's lookup functions -- especially the "binary search" option built into those functions (as opposed to the linear "exact match" search option)? https://support.office.com/en-us/art...8-93a18ad188a1

    Something like =VLOOKUP(2,$H$10:$I$14,2,TRUE)>=2 would return TRUE because 2 is between 1 and 3
    =VLOOKUP(4,$H$10:$I$14,2,TRUE)>=4 would return FALSE, because 4 greater than 3
    Then use that condition in an IF() function to return the desired value (1 or "" )
    Last edited by MrShorty; 02-28-2017 at 04:57 PM. Reason: Fix formula error
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    01-16-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    4

    Re: Avoid using nested IF

    I'm familiar with VLOOKUP, yes. But what if in the third row (say, AC3) I have a value like 34, which isn't near 21 but is in the same range (and has to be compared with the right extreme which is 45)? VLOOKUP won't find the correct range row and won't compare it with the column I value

    The numbers I'm giving are examples, in reality I have values like 623450 and I have to compare it with another values like 623400 and 623500

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Avoid using nested IF

    =VLOOKUP(34,$H$10:$I$14,2,TRUE) returns something other than 45? I would have to see that in a spreadsheet, because the expected behavior when lookup value is greater than the last value in the leftmost column is to return the value from the last row (45 in this case).

    I don't see why this approach won't work for larger numbers like you indicate.

  7. #7
    Registered User
    Join Date
    01-16-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    4

    Re: Avoid using nested IF

    Oh god, thank you SO MUCH. I had a problem with combined cells which changed the column count so VLOOKUP didn't find the proper values to the right.

    That's all. Problem solved. I love you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Nested Sumif's or how to sum data based on nested criteria
    By dlietz in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-07-2014, 11:36 AM
  2. How to reduces Nested IF code(144 Nested IF) to achive same functionality
    By jobseeker in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-09-2014, 09:45 AM
  3. ELSE IF function? *looking to avoid nested IF*
    By sonodelirii in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-27-2012, 06:40 PM
  4. [SOLVED] Avoid nested IF statement and bring columns together
    By deanstein in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-23-2012, 02:35 AM
  5. nested if statement- trying to avoid but don't know how
    By dumpster1985 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2012, 02:56 AM
  6. How can I avoid large, nested IF AND OR queries
    By sherlock99 in forum Excel General
    Replies: 3
    Last Post: 01-19-2011, 07:50 AM
  7. help on a nested countif/nested if formula
    By vickiemc in forum Excel General
    Replies: 2
    Last Post: 08-13-2008, 08:29 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1