+ Reply to Thread
Results 1 to 5 of 5

Formula to search range, without If / Then / Else

  1. #1
    Forum Contributor
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    376

    Formula to search range, without If / Then / Else

    I am not sure the right terminology to use to create the formula I am trying to write.

    I have a table with headings "Lower" "Upper" and "Return"

    What I want to create is basically to look at a value say in cell B2 and then determine where that value lies in the table (comparing it against the Lower number, and the Upper Number), and then in Cell B3 have it return the value in the Return column.

    Essentially

    Lower <= Input < Upper

    But Just trying to do it cleaner than a long drawn out If/Then.
    Attached Files Attached Files

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Formula to search range, without If / Then / Else

    VLOOKUP should work for this. Put the following formula in B3..
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Forum Contributor
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    376

    Re: Formula to search range, without If / Then / Else

    It is weird... When I do the vlookup in the xslx file attached, everything works perfectly. When I go to append that data into the main worksheet it needs to be in (which is a .xslm file), I get an error if the B2 value is not the EXACT value that appears in the i6:k16 range. So for example if the value in B2 is 5, I get a "Value not available" error. but if the value in B2 is 6, then it will return the value of 125 without an issue.

  4. #4
    Forum Contributor
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    376

    Re: Formula to search range, without If / Then / Else

    I solved my own question, and figured I would put it here just in case anybody else comes across is.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Formula to search range, without If / Then / Else

    I'm glad you were able to get it working. Thank you for the rep!

+ 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. Replies: 2
    Last Post: 02-16-2016, 03:23 PM
  2. Use array formula for range using search
    By hiker100 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-19-2015, 09:41 AM
  3. [SOLVED] formula to search a range
    By Rob (SA) in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-15-2014, 02:10 PM
  4. Is there a formula to search a range of cells and add whichever...
    By financeguy2014 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-30-2014, 01:58 PM
  5. Modify this Search Formula to search across a range of cells
    By zicitron in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-16-2013, 08:11 AM
  6. Formula To Search IP Range for Location
    By atnextc in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-03-2012, 10:36 AM
  7. Formula to search if a value is in range of 2 columns
    By tvanhooz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2011, 01:15 AM

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