+ Reply to Thread
Results 1 to 9 of 9

Look up a value to see if it falls within a range and assign a particular value

  1. #1
    Registered User
    Join Date
    09-12-2018
    Location
    Austin
    MS-Off Ver
    2016
    Posts
    3

    Look up a value to see if it falls within a range and assign a particular value

    Hi, I have some problems using the VLOOKUP function. I hope to seek you guys' advice.

    So, i used =VLOOKUP(C5,Sheet1!$A$1:Sheet1!$C$15,3) trying to assign a value according to the range table in sheet 1 look like the below table.

    Sheet 1 table
    Range from to assigned value
    1492521300 1492532100 1
    1492543800 1492554600 2
    1492608600 1492619400 3
    1492619400 1492630200 4
    1492630200 1492641000 5

    1492585980 2
    1492586460 2
    1492586940 2

    But something went wrong with the function that even 1492585980 doesn't fall in any range, it was still being assigned a value of 2.

    Anyone know and could tell me how i can resolve this problem?

    Thanks,
    Yee
    Last edited by crystalng; 09-14-2018 at 10:33 AM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Vlookup

    Try adding a ",FALSE" (without the quotes) at the end of your formula.

    Edit: Scratch that. I read too fast. I don't believe that VLOOKUP is what you need to use here.

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Vlookup

    None of the three numbers at the bottom of your post belong in any of the ranges in your "Sheet 1 table".

    I recommend creating a small representative sample of your data along with the desired results (which you can enter manually) based on that sample.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Vlookup

    I'm thinking that it will look something like this:

    =INDEX(Sheet1!C$1:C$15,MATCH(1,(Sheet1!A$1:A$15<=C5)*(Sheet1!B$1:B$15>=C5),0)) Ctrl Shift Enter

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Vlookup

    Hi, welcome to the forum

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (note: this change is not optional )
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    09-12-2018
    Location
    Austin
    MS-Off Ver
    2016
    Posts
    3

    Re: Look up a value to see if it falls within a range and assign a particular value

    hi 63falcondude,

    Im still trying to figure of the Vlookup function. So, I actually have to look up a value to see if it falls within a range and assign a particular value.

    For example, if a value fall between 1492857000 & 1492867800, we will assign a 15. The same goes to all other values. Right now, i used this function, but this function doesnt work really well for the red color.

    =VLOOKUP(C5,Sheet1!$A$1:Sheet1!$C$15,3)

    1492857060 15
    1492857480 15
    1492857960 15
    1492858380 15
    1492858860 15
    1492859340 15
    1492859760 15
    1492860240 15
    1492860660 15
    1492861140 15
    1492861560 15
    1492862040 15
    1492862460 15
    1492862940 15
    1492863360 15
    1492863840 15
    1492864260 15
    1492864740 15
    1492865160 15
    1492865640 15
    1492866060 15
    1492866540 15
    1492866960 15
    1492867440 15
    1492867860 15
    1492868340 15
    1492868760 15
    1492869240 15
    1492869660 15
    1492870140 15

    Range assign value
    1492803000 1492813800 13
    1492846200 1492857000 14
    1492857000 1492867800 15

    I think the problem is the VLOOKUP function only look up the beginning range...

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Look up a value to see if it falls within a range and assign a particular value

    Let's keep all help publicly on the forum and not in personal messages.

    As mentioned in post #2, VLOOKUP isn't the right tool here.

    I believe that the formula posted in post #4 will do what you are looking for after adjusting the ranges to suit your data.

    Here it is on your data from post #6.

    The formula was entered in B2.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-12-2018
    Location
    Austin
    MS-Off Ver
    2016
    Posts
    3

    Re: Look up a value to see if it falls within a range and assign a particular value

    hi 63falcondude,

    I tried the function. It works great with all the data on one sheet. But when i open 2 sheets ( one for the value pending to receive an assigned value, another is the range with defined assigned value), something is not working again. I tested for a few hours..

    So, here is my current function in D5 =INDEX(Sheet1!C$1:C$15,MATCH(1,(Sheet1!A$1:A$15<=C6)*(Sheet1!B$1:B$15>=C6),0))

    Sheet 1 contain the ranges and particular values.

    Thanks for your help! Very very appreciated.

    You rock.
    Attached Files Attached Files
    Last edited by crystalng; 09-14-2018 at 05:29 PM.

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Look up a value to see if it falls within a range and assign a particular value

    Two things:

    1) The formula in D5 refers to C6
    2) The formula was not array entered (Ctrl Shift Enter)

    So change the formula in D5 to this:
    =INDEX(Sheet1!C$1:C$15,MATCH(1,(Sheet1!A$1:A$15<=C5)*(Sheet1!B$1:B$15>=C5),0)) Ctrl Shift Enter

    Then drag it down column D. You will see that your first match is in cell D56.

+ 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: 5
    Last Post: 12-07-2016, 09:18 AM
  2. Replies: 3
    Last Post: 12-04-2014, 01:27 PM
  3. Replies: 0
    Last Post: 12-26-2013, 01:12 PM
  4. Display Cell within VLookup range that excel thinks matches the VLookup Value
    By headachexcelperson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2013, 04:56 PM
  5. how to bring all vlookup returns even with duplicate vlookup search keys
    By NYC4LIFE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-06-2013, 04:53 PM
  6. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  7. Replies: 5
    Last Post: 07-29-2009, 07:53 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