Results 1 to 6 of 6
Thread: Range lookup (sort of)

20110705, 02:58 #1
 Join Date
 Jul 2001
 Location
 Terneuzen, Netherlands
 Posts
 895
 Thanks
 0
 Thanked 0 Times in 0 Posts
Range lookup (sort of)
I have a range (sorted ascending) but with doubles (which need to stay as there's more data behind them), like:
1
1
1
2
2
2
2
3
3
6
6
6
Etc.
The size of the sub classes (the 1s, 2s, 3s, 6s, etc) is not always the same (here e.g. I have 3 ones, 4 two's...). Now I have the value 2.6 (from a calculation).
First I need to know (in Excel, not VBA if possible); which subclass is closest to this number. More precisely: which row does that start and where does it end (I need that to do a further lookup later with VLOOKUP).
So... subclass "3" is closest to 2.6. The range starts with the 8th element and the last is the 9th element...
I found a complex way to find the closest subclass (which fails e.g. for 2.5 which is exactly in the middle but I can work around that I hope). If there's a more solid way I'd like to learn.
What I need most is that IF I have 3, how to I find the first and last ocurrence...

20110705, 04:31 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,350
 Thanks
 4
 Thanked 228 Times in 210 Posts
You could use something like:
=MATCH(LOOKUP(ROUND(B1,0),$A$1:$A$11),$A$1:$A$11,0 )
to get the position of the first match and use COUNTIF to see how many there are and add that on (and subtract one!).Regards,
Rory
Microsoft MVP  Excel

20110705, 04:50 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
If we assume the lookup value (2.5,2.6,3, etc) is in Cell C1 and the range of interest is A1:A100 (change in formulas as desired), the lowest row of the closest match is the array formula (confirm with ctrlshiftend):
=MIN(IF(ABS($A$1:$A$100$C$1)=MIN(ABS($A$1:$A$100$C$1)),ROW($A$1:$A$100)))
The largest row with that value can be found with the array formula (confirm with ctrlshiftend):
=MAX(IF($A$1:$A$100=INDEX($A$1:$A$100,MIN(IF(ABS($ A$1:$A$100$C$1)=MIN(ABS($A$1:$A$100$C$1)),ROW($A$1:$A$100)))),ROW($A$1:$A$100)))
The first finds the minimum row whose absolute value of the difference is the smallest. The second finds the max row for that has the value from the first lookup. If you put the first calc in a cell (eg D1) you could use that in the formula instead (confirm with ctrlshiftend):
=MAX(IF($A$1:$A$100=INDEX($A$1:$A$100,D1),ROW($A$1 :$A$100)))
[Note if you have something in the middle like 2.5 it will find 2 not 3 since it finds the lowest row which matches). If you want the higher value 3 instead of 2 for 2.5 use for the largest row(confirm with ctrlshiftend):
=MAX(IF(ABS($A$1:$A$100$C$1)=MIN(ABS($A$1:$A$100$C$1)),ROW($A$1:$A$100)))
and use for the smallest row (confirm with ctrlshiftend):
=MIN(IF($A$1:$A$100=INDEX($A$1:$A$100,D1),ROW($A$1 :$A$100)))
to find the max row with the smallest absolute difference and then find the minimum row containing that value]
Steve

20110705, 04:56 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Rory,
[whisper]That formula will not work if you try and lookup a rounded value not in the list. If your lookup with that dataset is 5, it will not give the row for the first 6 but for the first value of 3...
Steve

20110705, 06:14 #5
 Join Date
 Jul 2001
 Location
 Terneuzen, Netherlands
 Posts
 895
 Thanks
 0
 Thanked 0 Times in 0 Posts
Steve, Rory,
Thanks for your feedback and suggestions. With the solutions provided, I was able to get what I was looking for.
Erik Jan

20110705, 08:19 #6
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,350
 Thanks
 4
 Thanked 228 Times in 210 Posts
Steve,
You are correct  that's what I get for posting in a rush as I'm on my way out. Apologies, ErikJan.Regards,
Rory
Microsoft MVP  Excel