modified vlookup help

  • Thread starter Thread starter tjb
  • Start date Start date
T

tjb

I don't know if this will be a vlookup or something similar but here's what I
need:

-user enters a number that could range anywhere from 0 to 22667.27
-formula should match whatever number the user entered against an applicable
range and then return a different number
-For example a user enters 2931.15
-Formula should return a value of "125" since the user defined number falls
between 2930.92 and 2954.54
-Formula should return a value of "126" if user enters 2970.12 because that
value falls between 2954.55 and 2978.18

I have a list of hundreds of different ranges like this going from 0 all the
way to 22667.27.

Please help! Thanks.
 
You havnt told from where 125 came from..If your data is sorted then check
out help on the function =LOOKUP()

If this post helps click Yes
 
See if this helps...

A1 = some number like 55

Lookup table in the range E1:G4

...E.....F.....G
...0....25....21
26...50.....35
51...75.....40
76............52

=VLOOKUP(A1,E1:G4,3)

Result = 40. 55 falls within the range 51 - 75 and returns the result from
column G.
 
There are three columns of data:
A1:2930.92
A2:2954.54
A3:125

If the user enters a number (or the number is derived from another formula)
that falls between the value in A1 and the value in A2, I want it to return
the value from A3.
 
=LOOKUP(2931.15,A:A,C:C)

OR

with the number in D1
=LOOKUP(D1,A:A,C:C)


If this post helps click Yes
 
If the input is in D1, then
=IF(MEDIAN(D1,A1,A2)=D1,125,"whatever answer you want if it isn't 125)
or
=IF(AND(D1>=A1,D1<=A2),125,"whatever answer you want if it isn't 125)
 
Back
Top