Results 1 to 3 of 3

Thread: Help with VLOOKUP

  1. #1

    Help with VLOOKUP



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I'm looking for some help using VLOOKUP or another formula that might be able to accomplish what I need to have done.

    Cell D2 is any whole number between 75000 - 2,000,000

    Column S is a list of whole numbers from 75000 up, which counts in 5000 increments.
    Column T is a corresponding factor to Column S. For example:
    75000 0.825
    80000 0.850
    85000 0.885
    90000 0.921
    95000 0.959
    100000 1.000
    And so on.

    Currently I'm using a basic VLOOKUP formula, =VLOOKUP(D2,S2:T115,2), however, this of course only allows the user to enter a number the exactly corresponds to a value in column S.
    What I need to do is develop the numbers in between each value in column S&T so that a user may enter any number (in increments of 100) in Cell D2 and get a correct factor.

    So basically if the user enters 82500 in cell D2 Excel needs to recognize that cells S2 & S3 are the cells in play, reference each of the factors in column T (.825 & .885), find the difference (.35), divide by 50 (.007) and then multiply by ((82500-S2)/100))=.175+T2

    Is there a way I can work that into a formula in Excel?
    Any assistance would be greatly appreciated!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =(INDEX($T$1:$T$6,MATCH(TRUE,INDEX($S$1:$S$6>=N1,0),0))-INDEX($T$1:$T$6,MATCH(2,INDEX(1/($S$1:$S$6<=N1),0))))/50*((N1-INDEX($S$1:$S$6,MATCH(2,INDEX(1/($S$1:$S$6<=N1),0))))/100)+INDEX($T$1:$T$6,MATCH(2,INDEX(1/($S$1:$S$6<=N1),0)))

    where N1 contains the input value (82500)


  3. #3
    Quote Originally Posted by NBVC View Post
    Try:

    =(INDEX($T$1:$T$6,MATCH(TRUE,INDEX($S$1:$S$6>=N1,0),0))-INDEX($T$1:$T$6,MATCH(2,INDEX(1/($S$1:$S$6<=N1),0))))/50*((N1-INDEX($S$1:$S$6,MATCH(2,INDEX(1/($S$1:$S$6<=N1),0))))/100)+INDEX($T$1:$T$6,MATCH(2,INDEX(1/($S$1:$S$6<=N1),0)))

    where N1 contains the input value (82500)
    That did it. Thanks so much for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •