<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}.font5 {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore
adding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl24 {text-align:center;}ruby {ruby-align:left;}rt {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-char-type:none; display:none;}--></style>Hi, sorry not really asked questions on here before, so unclear on the upload techniques.
The below example rows is what i am trying to achieve.
i have a value of 125, if it is ascending order, a lookup formula can tell me it sits in the range that will return the result "abc"
if that range is descending, the lookup formula can not produce same result, despite it still being true that 125 sits in the ABC range.
Hopefully that makes sense. Is there a way around this limitation on lookups?
| | | | | | | |
ASCENDING RANGE LOOK UP | | | | | | | |
| | | | | | | |
| Range | Range | Look Up Value | Return Value | Formula | | |
| 100 | 200 | 125 | "ABC" | "ABC" | | =LOOKUP(D6,B:C,E:E) |
<!--StartFragment--> <colgroup><col width="79"> <col width="39" span="2"> <col width="80"> <col width="73"> <col width="48"> <col width="11"> <col width="124"> </colgroup><tbody>
<!--EndFragment--></tbody>
<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}.font5 {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore
adding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl24 {text-align:center;}ruby {ruby-align:left;}rt {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-char-type:none; display:none;}--></style>
Descending RANGE LOOK UP | - doesnt work | | | | | |
| | | | | | |
| Range | Range | Look Up Value | Return Value | Formula | |
| 200 | 200 | 125 | "ABC" | #N/A | =LOOKUP(O5,M:N,P:P) |
<!--StartFragment--> <colgroup><col width="75" span="7"> </colgroup><tbody>
<!--EndFragment--></tbody>