Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    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...

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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 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. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    5 Star Lounger
    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

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

Posting Permissions

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