+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Consolidate many if/and formulas to one formula in one cell

  1. #1
    Registered User
    Join Date
    10-18-2010
    Location
    NJ
    MS-Off Ver
    Excel 2007
    Posts
    73

    Consolidate many if/and formulas to one formula in one cell

    Is there a way to combine these formulas into one cell? I currently have them in 12 different cells.

    =IF($I2<=Sheet1!$B$17,Sheet1!$C$17,"")

    =IF(AND($I2<=Sheet1!$B$16,$I2>Sheet1!$B$17),Sheet1!$C$16,"")

    =IF(AND($I2<=Sheet1!$B$15,$I2>Sheet1!$B$16),Sheet1!$C$15,"")

    =IF(AND($I2<=Sheet1!$B$14,$I2>Sheet1!$B$15),Sheet1!$C$14,"")

    =IF(AND($I2<=Sheet1!$B$13,$I2>Sheet1!$B$14),Sheet1!$C$13,"")

    =IF(AND($I2<=Sheet1!$B$12,$I2>Sheet1!$B$13),Sheet1!$C$12,"")

    =IF(AND($I2<=Sheet1!$B$11,$I2>Sheet1!$B$12),Sheet1!$C$11,"")

    =IF(AND($I2<=Sheet1!$B$10,$I2>Sheet1!$B$11),Sheet1!$C$10,"")

    =IF(AND($I2<=Sheet1!$B$9,$I2>Sheet1!$B$10),Sheet1!$C$9,"")

    =IF(AND($I2<=Sheet1!$B$8,$I2>Sheet1!$B$9),Sheet1!$C$8,"")

    =IF(AND($I2<=Sheet1!$B$7,$I2>Sheet1!$B$8),Sheet1!$C$7,"")

    =IF(AND($I2<=Sheet1!$B$6,$I2>Sheet1!$B$7),Sheet1!$C$6,"")


    Thanks,
    Steve

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: if(and

    Hello Steve, does this do it?

    =IF($I$2>$B$6,"",INDEX($C$6:$C$17,MATCH($I2,$B$6:$B$17,-1)))
    Audere est facere

  3. #3
    Registered User
    Join Date
    10-18-2010
    Location
    NJ
    MS-Off Ver
    Excel 2007
    Posts
    73

    Re: if(and

    Quote Originally Posted by daddylonglegs View Post
    Hello Steve, does this do it?

    =IF($I$2>$B$6,"",INDEX($C$6:$C$17,MATCH($I2,$B$6:$B$17,-1)))
    Man - I love you!

    Seriously...thanks. I really appreciate it. It works like a charm.

    Steve

  4. #4
    Registered User
    Join Date
    10-18-2010
    Location
    NJ
    MS-Off Ver
    Excel 2007
    Posts
    73

    Re: if(and

    Quote Originally Posted by daddylonglegs View Post
    Hello Steve, does this do it?

    =IF($I$2>$B$6,"",INDEX($C$6:$C$17,MATCH($I2,$B$6:$B$17,-1)))
    What does the "-1" to do the formula?

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Consolidate many if/and formulas to one formula in one cell

    I assume that B6:B17 are numbers in descending order. If that's the case then when you use MATCH with a third argument of -1 the formula returns the greatest number that's less than or equal to the lookup value, see Excel Help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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