Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding highest values | Excel Worksheet Functions | |||
Finding the highest value from two columns of data | Excel Worksheet Functions | |||
Finding the highest values | Excel Discussion (Misc queries) | |||
Finding the next highest value in a list | Excel Discussion (Misc queries) | |||
finding highest dollar amount | Excel Worksheet Functions |