Match Closest Results from Data Array

[TheRobsterUK]

Suppose I have 2 columns of data. Column 1 holds a set of values and column 2 holds the probability of any one of these values occuring. So the data would look like:

Column 1................Column 2
-1,256.......................0.02
-567..........................0.04
345...........................0.10
1,234........................0.23
3,234........................0.39

Etc...

What I need are two sets of formulas. The first set would take a user-specifed value and find the closest value in column 1 that is greater than or equal to the value entered by the user, and then return the looked up value and and the corresponding probability from column 2.

The second set of formulas would take the same user-specifed value and find the closest value in column 2 that is less than or equal to the value entered by the user, and then return the looked up value and the corresponding probability from column 2.

E.g. in the above data table, tell the spreadsheet to look up a value of 1,500. The closest existing value that is greater than or equal to 1,500 is 3,234 and the corresponding probabiltiy is 0.39 so return these values. The closest existing value that is less than or equal to 1,500 is 1,234 and the corresponding probability is 0.23 so return these values.

I think I could use the VLOOKUP function for the second set of formulas but it doesn't work when trying to look up the greater than or equal to value, just the less than or equal to value.

Also, any formulas would have to be able to work with negative numbers in column 1 (in the spreadsheet the range can be any set of real numbers).

Many thanks
-Rob

[Domenic]

Assuming that A1:B5 contains your data, and D1 contains your lookup
value, such as 1500, try the following...

1) Less Than or Equal To

=LOOKUP(D1,A1:A5)

and

=LOOKUP(D1,A1:A5,B1:B5)

2) Greater Than or Equal To

E1:

=MIN(IF(A1:A5>=D1,A1:A5))

....confirmed with CONTROL+SHIFT+ENTER

F1:

=VLOOKUP(MIN(IF(A1:A5>=D1,A1:A5)),A1:B5,2,0)

....confirmed with CONTROL+SHIFT+ENTER

or

=VLOOKUP(E1,A1:B5,2,0)

Hope this helps!

In article <[email protected]>,
TheRobsterUK
<[email protected]> wrote:

> Suppose I have 2 columns of data. Column 1 holds a set of values and
> column 2 holds the probability of any one of these values occuring. So
> the data would look like:
>
> Column 1................Column 2
> -1,256.......................0.02
> -567..........................0.04
> 345...........................0.10
> 1,234........................0.23
> 3,234........................0.39
>
> Etc...
>
> What I need are two sets of formulas. The first set would take a
> user-specifed value and find the closest value in column 1 that is
> -greater than or equal to- the value entered by the user, and then
> return the looked up value -and- and the corresponding probability from
> column 2.
>
> The second set of formulas would take the same user-specifed value and
> find the closest value in column 2 that is -less than or equal to- the
> value entered by the user, and then return the looked up value -and-
> the corresponding probability from column 2.
>
> E.g. in the above data table, tell the spreadsheet to look up a value
> of 1,500. The closest existing value that is greater than or equal to
> 1,500 is 3,234 and the corresponding probabiltiy is 0.39 so return
> these values. The closest existing value that is less than or equal to
> 1,500 is 1,234 and the corresponding probability is 0.23 so return
> these values.
>
> I think I could use the VLOOKUP function for the second set of formulas
> but it doesn't work when trying to look up the -greater than or equal
> to- value, just the -less than or equal to- value.
>
> Also, any formulas would have to be able to work with negative numbers
> in column 1 (in the spreadsheet the range can be any set of real
> numbers).
>
> Many thanks
> -Rob

[TheRobsterUK]

Domenic,

That works just fine!

Thanks
-Rob