Calculate a number based on an interval

[Vitalie Ciobanu]

It seems that I can't figure it out for myself and need some help.
For example I have a random number from 0 to 1:
0.10
0.11
0.24
0.08
0.14

I also have two columns with interval numbers (min and max):
min max
0.00 0.01
0.01 0.05
0.05 0.14
0.14 0.28

And I have one column more with simple numbers:
20
40
60
80
100

Now what I need. If I will take the first random number (0.10), it matches
the third interval (0.05-0.14) and after this I have to look for the number
that corresponds to this interval, in my case it is 60.
I can't figure the formula that looks in what interval that matches the
random number. Can someone help me with a hint or with a formula?
Thank you!

 

[davesexcel]

if you could sort all your columns then you could use VLOOKU

 

[Bernard Liengme]

The random values are in A2:A6
The Min values in B2:B6 (Max values not needed)
This formula =INDEX($D$2:$D$6,MATCH(A2,$B$2:$B$5,1))
returns the required 60
best wishes

 

[Biff]

Hi!

Your ranges overlap:

min max
0.00 0.01
0.01 0.05
0.05 0.14
0.14 0.28

For example: 0.05 can't be both the max for one interval and the min for
another interval.

Try this table:

min.................interval
0........................20
0.02...................40
0.06...................60
0.15...................80
0.29..................100

Then use this formula:

A1 = random number = 0.10

=VLOOKUP(A1,H$1:J$5,2)

Where H$1:J$5 is the above table. 2 refers to the Interval column of that
table.

Biff

 

[Vitalie Ciobanu]

to davesexcel: I can not sort the columns because they are randomly
generated and it doesn't meet my needs. Thanks anyway!

to Bernard Liengme: Thank you for answering. Although I do not understand
what the formula looks for in D26.

to Biff: Your example seems to be the best! Now I need to make some changes
and make my problem work.

Thanks to all for helping me!!!! Have a nice day!