+ Reply to Thread
Results 1 to 4 of 4

Offsetting Referenced Cell After Finding Value Equal to or Greater Than X

  1. #1
    Registered User
    Join Date
    04-30-2008
    Posts
    67

    Question Offsetting Referenced Cell After Finding Value Equal to or Greater Than X

    Good Morning Everyone!

    I've got a formula that almost works - I can get the right value, but I want to get the value of the cell that's in the same row, but in a different column. I've tried everything I can think of, but my formula isn't getting the row number but the value, so OFFSET doesn't work.

    My overall goal is to find the right piece of equipment depending on the tonnage (AB1), refrigerant type (AC7:AL7) and the temperature (AC8:AL8). I've got it to where it'll look down the column for the first value that's equal to or greater than the value of AB1, but I can't get it to return the value of the cell that's in in Col AB. For example, if the ton value is 104 (AB1), the refrigerant is R-22, and the temp is 40, then the correct part would be in Cell AB12 (S-7727KW).

    Any gurus know of a way to return the offset cell of the returned value? I'll figure out how to add in the conditionals of the refrigerant/temp/tonnage later, but I want to get this out of the way.

    Thanks for any direction you can give

    Excel_Formula_Example-01.jpg

  2. #2
    Registered User
    Join Date
    04-30-2008
    Posts
    67

    Re: Offsetting Referenced Cell After Finding Value Equal to or Greater Than X

    Attached is a workbook that has this data, not just a jpg as in my previous post.Excel Example-01.xls

  3. #3
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: Offsetting Referenced Cell After Finding Value Equal to or Greater Than X

    hi there,

    use this ARRAY formula to get S-7727KW:

    Please Login or Register  to view this content.
    does that work for you?

    UPDATE:

    in case that does work, here is one long-ish, but dynamic, ARRAY formula to get you to the correct PART NUMBER without the need for ROW 15 in your sample workbook:

    Please Login or Register  to view this content.
    Last edited by icestationzbra; 03-27-2013 at 11:59 AM.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  4. #4
    Registered User
    Join Date
    04-30-2008
    Posts
    67

    Re: Offsetting Referenced Cell After Finding Value Equal to or Greater Than X

    That first one seemed to work just fine. Thank you Now I just need to figure out how to apply this to multiple conditions (tons, temp, refrigerant).

    Thanks again for your 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