Finding Next Highest Value in LookupTable?
 

Hi,

I want to create a formula that would compare a calculated number (C13) and
compare it against the numbers listed in A1. If C13 doesn't doesn't match any
of the listed numbers then the idea is to settle for the closest but higher
number in A1 and return the corresponding info listed in B1.

So for example, if C13 is calculated as 12.22 then it should return "d2".

A1 B1
10.83 d1
13.82 d2
16.27 d3
18.47 d4

Thanks for any ideas/help.

Les

Finding Next Highest Value in LookupTable?
 

First sort your table by column A in descending order, then
=INDEX(B:B,MATCH(C13,A:A,-1))
will give d2.

Regards,
Stefi


€žLes€� ezt Ã*rta:

Hi,

I want to create a formula that would compare a calculated number (C13) and
compare it against the numbers listed in A1. If C13 doesn't doesn't match any
of the listed numbers then the idea is to settle for the closest but higher
number in A1 and return the corresponding info listed in B1.

So for example, if C13 is calculated as 12.22 then it should return "d2".

A1 B1
10.83 d1
13.82 d2
16.27 d3
18.47 d4

Thanks for any ideas/help.

Les

Finding Next Highest Value in LookupTable?
 

Worked like a charm! Thanks, Stefi.

Les

"Stefi" wrote:

First sort your table by column A in descending order, then
=INDEX(B:B,MATCH(C13,A:A,-1))
will give d2.

Regards,
Stefi


€žLes€� ezt Ã*rta:

Hi,

I want to create a formula that would compare a calculated number (C13) and
compare it against the numbers listed in A1. If C13 doesn't doesn't match any
of the listed numbers then the idea is to settle for the closest but higher
number in A1 and return the corresponding info listed in B1.

So for example, if C13 is calculated as 12.22 then it should return "d2".

A1 B1
10.83 d1
13.82 d2
16.27 d3
18.47 d4

Thanks for any ideas/help.

Les

Finding Next Highest Value in LookupTable?
 

You are welcome! Thanks for the feedback!
Stefi

€žLes€� ezt Ã*rta:

Worked like a charm! Thanks, Stefi.

Les

"Stefi" wrote:

First sort your table by column A in descending order, then
=INDEX(B:B,MATCH(C13,A:A,-1))
will give d2.

Regards,
Stefi


€žLes€� ezt Ã*rta:

Hi,

I want to create a formula that would compare a calculated number (C13) and
compare it against the numbers listed in A1. If C13 doesn't doesn't match any
of the listed numbers then the idea is to settle for the closest but higher
number in A1 and return the corresponding info listed in B1.

So for example, if C13 is calculated as 12.22 then it should return "d2".

A1 B1
10.83 d1
13.82 d2
16.27 d3
18.47 d4

Thanks for any ideas/help.

Les