# Thread: Range lookup (sort of)

1. ## 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...

2. 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!).

3. 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 ctrl-shift-end):
=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 ctrl-shift-end):
=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 ctrl-shift-end):
=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 ctrl-shift-end):
=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 ctrl-shift-end):
=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

4. 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

5. Steve, Rory,

Thanks for your feedback and suggestions. With the solutions provided, I was able to get what I was looking for.

Erik Jan

6. Steve,
You are correct - that's what I get for posting in a rush as I'm on my way out. Apologies, ErikJan.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•