Previous 1 2 17 Replies Latest reply: Nov 6, 2012 12:18 PM by Badunit
Level 1 (65 points)

Hiya folks.

Just wondering if I can use a LOOKUP for a value in a range.  Example:

Lookup value:  2300

Range table:

Minimum
MaximumAssociate
1999alpha
10002499bravo
25004999charlie
50009999delta
1000019999echo
2000099999foxtrot

How can I look up the value 2300 and return its associate?

Cheers

Mac Pro, OS X Mountain Lion (10.8.2), Hypercard UG!
• Level 6 (16,945 points)

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"

• Level 1 (65 points)

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

• Level 7 (29,610 points)

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

• Level 1 (65 points)

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

• Level 7 (29,610 points)

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

• Level 1 (65 points)

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

• Level 7 (29,610 points)

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

• Level 1 (65 points)

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.

• Level 7 (29,610 points)

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

• Level 1 (65 points)

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.

• Level 7 (29,610 points)

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

• Level 1 (65 points)

Sure, and I have to use it.  I'd just do it another way, that's all.

• Level 6 (16,945 points)

You may provide feedback to Apple by using the menu item (in Numbers):

"Numbers > Provide Numbers Feedback..."

• Level 1 (65 points)

Yep, I often use these.  I rarely see things fixed.  Mail still has problems that were never addressed.

Previous 1 2