Previous 1 2 Next 17 Replies Latest reply: Nov 6, 2012 12:18 PM by Badunit Go to original post
  • Badunit Level 6 Level 6 (11,400 points)

    As a software developer, you must often encounter situations where you have to develop routines, where there isn't already a built-in function in the language that does exactly what you want. Well, it's the same way with spreadsheets. Spreadsheets are a type of programming language.


    The problem with doing a "range lookup" using a lookup function is that your table must have ranges that are contiguous; no gaps or missing numbers (i.e., you can have ranges like 1-999, 1000-1999, etc. but not ranges like 1-100, 1000-1100, etc where some numbers are not covered by a range). The lookup, as you have discovered, only looks at the lower number in the range. But, on the other hand, the ranges in your table are contiguous so there is no need to do a range lookup when you can much more easily just look for the lower number in the range.


    Nevertheless, here is a formula that will do an actual range lookup:


    =IF(MATCH(D2,A2:A7,1)=MATCH(D2,B2:B7,-1),OFFSET(C2,MATCH(D2,A2:A7,1)-1,0),"Not Found")


    or, to cover instances where the value to be looked up is way out of range:


    =IFERROR(IF(MATCH(D2,A2:A7,1)=MATCH(D2,B2:B7,-1),OFFSET(C2,MATCH(D2,A2:A7,1)-1,0 ),"Not Found"),"Not Found")


    In English (or like a software routine):

    1. Find the row in column B that has the closest number less than or equal to the lookup value (in cell D2)
    2. Find the row in column C that has the closest number greater than or equal to the lookup value
    3. If the two rows are the same then return the value in column C of that row else return "Not Found"


    This formula requires non-overlapping ranges.  For instance you cannot have a range of 0-2000 and another range of 200-300.That shouldn't be a problem because it would be ambiguous anyway.

  • BioRich Level 1 Level 1 (65 points)

    You are correct.  In this instance, some would argue that the current "tag" doesn't go far enough.  So they would replace the functionality of the tag instead of providing additional tags for further povision.  The problem here is that the documentation is slim enough that you might miss some endearing features if you do indeed rewrite such a function.  So, my solution is to just add another tag to provide the functionality that I need, and keep my own docs on my own tags.  That way, encapsulation is more reliable as I'm not trying to jam a feature set into a single tag.  Yes, the tag count grows, but it's documented well.  Documentation has always been a problem for languages.  It's one of the reasons I'm migrating languages now.  Development resources are a major part of finding out how to get around obstacles. 


    There is no iterate or routine for this type of programming.  It's more FileMaker style. I was thinking range, and that's what I searched for, and nothing came up.  I think in terms of iteration than qualifying a test cell.  That to me feels like a workaround that we had to do in FileMaker for so long.  It feels super dirty. 

  • Badunit Level 6 Level 6 (11,400 points)

    You are right, there is no iteration, at least not the way you are thinking. Iteration requires putting the same calculations in multiple columns (or rows) where each columngets its input from the one before.  100 columns = 100 iterations. Or you can write a script that does iteration.

Previous 1 2 Next