Lookup formula - can handle descending ranges!? Solution anyone?

[colman12]

Hi,

Using a lookup formula to find where a value sits in a range.

i.e.

100 - 200 - "ABC" ---- 104 - Look up 104, between those ranges and return ABC - works fine

if the range is 200 ---- 100 - if NAs

Any solutions or can i use a different formula?

Thanks

Colin

 

[Scott T]

If you can use exact match then the order of the data does not matter

where C2 has the value you are looking up. A2:B102 is your range to look in, looking in the 2nd column and, 0 for exact match

=VLOOKUP(C2,A2:B102,2,0)

 

[colman12]

Hi Scott,

Thanks for helping.

I am not your approach is solving my issue.

I need to find where the lookup value sits within a range, hence why i am using a lookup.

I think a vlookup can only give me an exact match, not find which range the look up value sits within.

Is there is any way around this?

Let me know if am not being clear enough?

 

[arthurbr]

Hi Scott,



I need to find where the lookup value sits within a range, hence why i am using a lookup.

I think a vlookup can only give me an exact match, not find which range the look up value sits within.

What do you mean exactly by "sits within a range"? The cell address?

 

[AliGW]

Provide some sample data in tabular format to show us what the spreadsheet looks like.

VLOOKUP has two closing arguments to choose from:

FALSE (or 0) gives an exact match
TRUE (or 1) gives an approximate match, but the lookup values must be in ascending order in the lookup column

 

[colman12]

<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-ignoreadding; 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?

[TABLE="width: 493"]
<!--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>[TR]
[TD="width: 79"][/TD]
[TD="width: 39"][/TD]
[TD="width: 39"][/TD]
[TD="width: 80"][/TD]
[TD="width: 73"][/TD]
[TD="width: 48"][/TD]
[TD="width: 11"][/TD]
[TD="width: 124"][/TD]
[/TR]
[TR]
[TD="colspan: 4"]ASCENDING RANGE LOOK UP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Range[/TD]
[TD] Range[/TD]
[TD] Look Up Value [/TD]
[TD] Return Value[/TD]
[TD="colspan: 2"] Formula[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]125[/TD]
[TD] "ABC"[/TD]
[TD="class: xl24"]"ABC"[/TD]
[TD="align: right"] [/TD]
[TD]=LOOKUP(D6,B:C,E:E)[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]


<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-ignoreadding; 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>[TABLE="width: 525"]
<!--StartFragment--> <colgroup><col width="75" span="7"> </colgroup><tbody>[TR]
[TD="width: 225, colspan: 3"]Descending RANGE LOOK UP[/TD]
[TD="width: 75"]- doesnt work[/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Range[/TD]
[TD]Range[/TD]
[TD]Look Up Value[/TD]
[TD]Return Value[/TD]
[TD]Formula[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]125[/TD]
[TD]"ABC"[/TD]
[TD="class: xl24"]#N/A[/TD]
[TD]=LOOKUP(O5,M:N,P:P)[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[AliGW]

What's the bigger picture here? There may be a solution, but as you are only giving us 'keyhole' views onto your data, it's hard to advise. Grab the ForumTools add-in for Excel (link in my sig line) and use that to post a larger sample of what's in your workbook. We've already confirmed that LOOKUP and VLOOKUP with an approximate match work only on ascending ranges.

 

[Tetra201]

Maybe

=LOOKUP(-O5,-M5:N5,P5:P5)

 

[colman12]

Hi all, thanks Ali, i will download the add in.

However, Tetra201 may have solved! I will try this tweak out and let you know if it works.

If not, I will post a proper excel example of what I am trying to achieve.

 

[colman12]

Thanks all, looks like putting the "-" into the lookup, works a treat!