+ Reply to Thread
Results 1 to 5 of 5

Finding values between entries in Hlookup

  1. #1
    Registered User
    Join Date
    01-10-2012
    Location
    Boulder, CO
    MS-Off Ver
    Excel 2010
    Posts
    3

    Finding values between entries in Hlookup

    I have a two rows of cells where one row is the amount of a behavior (e.g. number of calories eaten) and the second row is points you get for that amount of the behavior. A shortened example is:
    Calories 1000 1200 1400 1800
    Points +100 +50 0 -35

    I want to be able to lookup the Points for a given level of calories. I know I can use HLookup, but when I do that and enter a Calories value not in the table (e.g., 1100) I get the Points associated with the lower calorie value, i.e. 100 points rather than the value I want which is halfway between the two tabled values, i.e., +75.

    Any ideas on how I can most easily get the correct values in between the tabled values?

    Thanks,

    Bob Pritchard

  2. #2
    Registered User
    Join Date
    01-10-2012
    Location
    colorado, usa
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Finding values between entries in Hlookup

    Bob,
    it would probably be best to add in all possible values of calories in the table and there corresponding values, even if the values will repeat. AT LEAST IF YOU WANT TO KEEP USING THE HLOOKUP FUNCTION THAT IS.

  3. #3
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Finding values between entries in Hlookup

    Assume B1:Z2 contains data. A4 is lookup value.

    May be this???

    =IF(ISNA(MATCH(A4,B1:Z1,0)),(LOOKUP(A4,B1:Z2)+INDEX(B2:Z2,MATCH(A4,B1:Z1)+1))/2,LOOKUP(A4,B1:Z2))

    Or,

    For non exact MATCH Looking from starting to column <Lookup_value+1

    Please Login or Register  to view this content.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  4. #4
    Registered User
    Join Date
    01-10-2012
    Location
    Boulder, CO
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Finding values between entries in Hlookup

    Hi Bryan:
    Thanks for this.
    Is there a better way to do this than Lookup? (I see you are in Colorado. I'm near Boulder.)
    Bob

  5. #5
    Registered User
    Join Date
    01-10-2012
    Location
    Boulder, CO
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Finding values between entries in Hlookup

    Thanks Haseeb. I don't actually know what this function does.... Can you explain it a bit to a novice like me?

    Bob

+ 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