+ Reply to Thread
Results 1 to 4 of 4

Range of data and IF then problem maybe Vlookup awell

  1. #1
    Registered User
    Join Date
    10-09-2017
    Location
    Brisbane, Australia
    MS-Off Ver
    2016
    Posts
    3

    Range of data and IF then problem maybe Vlookup awell

    I have a table of data which provides ranges that my inputs can fall between

    "Table"
    1600
    1470
    1330
    1200
    1070
    930
    820
    780
    680
    590

    Basically when I get a number say 1220, I want to check which values it is in between and then compute the percentage the 1220 is of the range.
    For example
    IF 1220 is in the range of the "table", find what two values it is between which is 1330 and 1200.
    then take the number above 1220 which is 1330 and the number below 1220 which is 1200 and perform this calculation
    (1330-1220)/(1330-1200) to get a percentage of the range.

    Cheers
    Any ideas would be appreciated.

  2. #2
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 2010
    Posts
    1,234

    Re: Range of data and IF then problem maybe Vlookup awell

    try this
    where C4 is your number
    and F1:F10 is your range (replace to match wherever your data is)

    Please Login or Register  to view this content.
    of course this gives an error either above or below your range
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.

  3. #3
    Registered User
    Join Date
    10-09-2017
    Location
    Brisbane, Australia
    MS-Off Ver
    2016
    Posts
    3

    Re: Range of data and IF then problem maybe Vlookup awell

    Im getting a #REF error with this
    ExcelPRoblem.png

  4. #4
    Registered User
    Join Date
    10-09-2017
    Location
    Brisbane, Australia
    MS-Off Ver
    2016
    Posts
    3

    Re: Range of data and IF then problem maybe Vlookup awell

    Never mind I resolved it, just had to sort the columns in ascending order.

    Cheers

+ 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: 4
    Last Post: 05-30-2017, 10:19 AM
  2. [SOLVED] VLOOKUP and Fill Handle Problem: Selected Range vs. Named Range
    By Menenius in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-12-2016, 08:50 PM
  3. Named Range Problem within a VLookup
    By Renleff in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-15-2014, 05:47 PM
  4. Problem using an named range in VLOOKUP
    By stubbsj in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-09-2013, 01:18 PM
  5. Macro - Vlookup function, problem with range if only one cell contains data
    By Tapyr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-17-2013, 07:20 AM
  6. VLOOKUP data format problem
    By sss.seg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-02-2012, 04:43 PM
  7. problem using Range Names in VLOOKUP
    By Dee in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-28-2006, 03:00 PM

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