Hiya folks.
Just wondering if I can use a LOOKUP for a value in a range. Example:
Lookup value: 2300
Range table:
| Minimum |
Maximum | Associate |
|---|---|---|
| 1 | 999 | alpha |
| 1000 | 2499 | bravo |
| 2500 | 4999 | charlie |
| 5000 | 9999 | delta |
| 10000 | 19999 | echo |
| 20000 | 99999 | foxtrot |
How can I look up the value 2300 and return its associate?
Cheers
Mac Pro, OS X Mountain Lion (10.8.2), Hypercard UG!
Bio,
This may work for you:
In the table on the right:
A1 is the value to lookup in the table on the left.
B1=VLOOKUP(A1, Table 4 :: A:C, 3)
The table on the left is named "Table 4"
Bio,
This may work for you:
In the table on the right:
A1 is the value to lookup in the table on the left.
B1=VLOOKUP(A1, Table 4 :: A:C, 3)
The table on the left is named "Table 4"
Hi Wayne. Thanks for the reply.
Your solution worked. In fact, if you even take out the middle column, it works as well. I did a bit of messing about and found that out. But thanks for the lead!
Cheers
Hi Rich,
Only the threshold (ie. minimum) values need to be specified. Here, the Ranks table contains the same minimums (with one exception) as your sample table. I've left the Maximum column in but left it empty:
On the Results table, I've used LOOKUP to retrieve the associate values for a sampling of values, including the 2300 that you specified.
Formula:
Results::B2: =LOOKUP(A2,Ranks :: $A,Ranks :: $C)
Filled down to B7
LOOKUP looks for a 'close match', defined as "the largest value smaller than or equal to the search value." For a search value of 2300, LOOKUP finds 1000, and returns 'bravo' from the same row.
Wayne's formula should give the same results. I tend to reserve VLOOKUP for cases where I want the formula to find an 'exact match' rather than the 'close match' needed here. VLOOKUP can be set to demand either a 'close match' (the default) or an 'exact match'; LOOKUP always accepts a 'close match.'
Regards,
Barry
Regards,
Barry
That kind of functionality helps a lot. Might want to have more control though, or something that's clearer. I'd rather a larger set of functions than reviewing the documents for the scope on a function.
Anyway, it works. I am happy.
Cheers
That kind of functionality helps a lot. Might want to have more control though, or something that's clearer. I'd rather a larger set of functions than reviewing the documents for the scope on a function.
OK, I'm mystified.
How would "a larger set of functions," presumably covering a wider 'scope,' make it less necessary or less useful to 'review the documents'?
Regards,
Barry
I'm a software developer. My approach to this would be to have a function whose goal is to specifically apply this type of range lookup. Trying to solve too many items in a single function can inherently close its performance in specific cases. Second, I didn't find anything related to a range lookup. That might be just a result of the documentation. I used the word range and found nothing. I searched on the Google, and still nothing. So I posted here.
Cheers
My approach to this would be to have a function whose goal is to specifically apply this type of range lookup.
Isn't this specifically what LOOKUP does?
LOOKUP searches a list of values in a specified column and finds the largest value that is less than or equal to the search value, then returns the value from the same row of a specified column.
With LOOKUP (and with VLOOKUP or HLOOKUP when the 'close-match' argument is omitted or left at its default value), all lookups are 'range lookups' in the sense that applies in this case.
Regards,
Barry
Sure. But it isn't an all encompassing sitution. That's why it's also broken up into 4 functions so far.
Given that I was thinking "range", and using the same word to search the function library, it passed me. Providing functionality is one thing, having the user find its functionality is another thing. If they don't find it, they either think it's not there, or it's "somewhere hidden". Software of all kinds, always has demonstrated lacklustre documentation. Your argument is that it's encompassed, yet I point to 4 functions that hold all the lookups. So it's inbetween. I'd rather the functions broken out. There are indeed differences between discrete and indescrete lookups.
You seem to have conflicting desires here.
You'd "rather have a larger set of functions." Your "approach to this would be to have a function whose goal is to specifically apply this type of range lookup."
But LOOKUP, which does "apply to this type of range lookup", "isn't an all encompassing sitution."
You note that you "point to 4 functions that hold all the lookups." That may indeed be true, but although you do mention in the same post "it's also broken up into 4 functions so far," you don't 'point to' any of the four functions in this thread. You mentioned LOOKUP in your first post, Wayne offered a solution using VLOOKUP, I offered one using LOOKUP and commented on a difference between LOOKUP and VLOOKUP that made it more useful if the search was for exact value matches.
Your mention of the lacklustre documentation is, as you say, generally true of software. I'm inferring from what you've written that "function library" means the built in Function Browser. While that feature is great for getting the syntax right when writing a formula, it does come up a bit short on providing descriptions and examples for functions and their uses. You'll find a bit more in the downloadable iWork Formulas and Functions User Guide, which you can download via the Help menu in Numbers '09.
I wouldn't recommend searching on 'range', though. That word appears 258 times in the edition of the guide I'm using, and the great majority of these refer to a range of cells. 'lookup', the other word in the pair you mentioned will give you far fewer (52), but far more relevant hits, most of them clustered in Chapter 9: Reference Functions.
If by "discrete" and "indescrete" you are referring to searches for "exact" and "close" matches, the difference is in the setting of a single argument in VLOOKUP or HLOOKUP. I don't see how hiving this option off into two more separate functions would be useful. If you do think it a useful expansion, then make that suggestion to Apple via the Feedback channel. Choose Provide Numbers Feedback from the Application menu (the "Numbers" menu in Numbers), and send a Feature Enhancement request.
Regards,
Barry
No, you're missing my references. My "desires" are what I stated. You've mixed up my references with my desires.
See, all of this is a one to many relationship. The assumption that it means "from this point on-positive" is just wrong. LOOKUP is a database relationship. The 4 functions are LOOKUP, VLOOKUP, HLOOKUP, and MATCH.
So, when I searched for "range", 70 items came up. For a function of a search range, perhaps you think this is indicative of a range? Really? How from your presentation here does it mean that it's anything useful from any regular user of Numbers?
Your reply means that I spend how much time in what documention? Really? Have you ever considered the user of Numbers? I'm not sure of how much of a true software developer you are, but I will say this. This software is consumer level stuff. Consumer 100%. Any developer of a serious language will want an instant, repetitive, fully accessible Reference of all functionality, which explains what is where. Numbers...does not. It fails.
I would recommend spending some time in a real language where developers expect to know where definitions of functionality are presented. Numbers is a desktop application that is obviously half-tagged. The functionality of "range" lookup, should obviously have the tag "range" built into the function. Numbers, does not have it.
Your recommendation of "not searching on 'range'" is...not good. Not recommended. See, computers are our slaves. Making recommendations for searches...is backwards. And, your "guide" isn't my guide. Chapter 9 didn't help me. Ever. Fail. So, at this moment, multiple fails upon...Apple. And no, not everybody is asking you nor your own manuals.
Giving feedback is a bit past the point of having explanation upon its current software. Why would anybody provide feedback on software that apparently works, yet isn't documented? That's just an introduction to a (perhaps non-answered) debate on what is already there, yet unfounded, thanks to tech "writers" that forgot or chose not to enter that functionaltiy.
How many have provided feedback by choosing "Feedback..." and saying "make documentation, fools"?
Software isn't intuitive, as a developer. Nor is it by any user. This is just another example of where documentation doesn't come close to explaining how to get past a big wall. Hence the user stalls, and tech gets another bad name.
Obviously we are coming at this from very different viewpoints. This is a user-to-user forum. My viewpoint is that of a user of Numbers, yours is that of a developer of software.
I am not, nor have I claimed to be a software developer, true or otherwise. You state that this software (Numbers) is consumer level stuff. That's an accurate description. Lack of reference aside, there's a distinct possibility that Numbers may be unable to satisfactorily perform to fit your needs. Should it indeed prove to be the wrong tool, you should be looking elsewhere for a more suitable one.
As a user, my view of the software (and of the documentation) is that 'it is what it is.' The F&F User Guide is not, to my knowledge, tagged, but is searchable. Using the search tool available, better results are obtained if one gives some consideration to the weaknesses of the system. Computers may be our slaves, but what they do for us depends on what they know how to do, There's not much point in requesting "Tea, Earl Grey, hot!" from a slave who has no means of picking up the tea bag, even if it is capable of running hot enough to boil the water.
Cheers,
Barry
Sure, and I have to use it. I'd just do it another way, that's all.
You may provide feedback to Apple by using the menu item (in Numbers):
"Numbers > Provide Numbers Feedback..."
Yep, I often use these. I rarely see things fixed. Mail still has problems that were never addressed.
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):
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.
Lookup in Range?
