lookup tables

[RagDyer]

But, what I can't seem to get, is what the OP mentioned as a preference ...
namely ... the *higher* value when the lookup value is *exactly between* the
integers in the datalist.

 

[RagDyer]

I should mention ... In a *sorted* list!

Of course, unsorted, if the larger value is displayed first, it will be the
returned value.

 

[Roger Govier]

Hi RD

Now I recall why I was trying -1 rather than 0.
With data of 10,12,18,22,33 in A1:A5 and with 20 entered in B1
With -1 it returns 22 as the result (what the OP wanted)
With 0 it returns 18 as the result.

 

[RagDyer]

It doesn't do that for me!

 

[Dave Peterson]

How about this array formula:

=MAX(IF(ABS($A$1:$A$5-$B$1)=MIN(ABS($A$1:$A$5-$B$1)),$A$1:$A$5))

(I didn't read/notice the requirement to choose the largest value that was
closest to the given value.)

 

[Roger Govier]

Hi RD and Dave

I just don't believe it. This morning, using -1 does not produce the
same result.
Max - it behaves as you say
Dave - Your formula cracks the problem.
Me - I'm off to the Opticians!!!

 

[Phil Newman]

Thanks very much! all the formulas work very well, i think i'll use the
last one (it's shorter!) and seems to do what i want it to, which is
great.

thanks for all your help.

Phil

 

[Roger Govier]

Hi Phil

Thanks for the feedback (appreciated by all).
I'm sorry the whole thread got so protracted through my inadequacies!!

--
Regards

Roger Govier

Thanks very much! all the formulas work very well, i think i'll use
the
last one (it's shorter!) and seems to do what i want it to, which is
great.

thanks for all your help.

Phil

 

[Dave Peterson]

But it got resolved by your tenaciousness.

Hi Phil

Thanks for the feedback (appreciated by all).
I'm sorry the whole thread got so protracted through my inadequacies!!

 

[Pete_UK]

Isn't that tenacity? <bg>

Pete

But it got resolved by your tenaciousness.

 

[Dave Peterson]

I had to check my spelling first (just in case the CQC (Candian Quality Council)
was lurking!

tenacious

tenacious (te-nâ´shes) adjective
1. Holding or tending to hold persistently to something, such as a point of
view. See synonyms at STRONG.
2. Holding together firmly; cohesive: a tenacious material.
3. Clinging to another object or surface; adhesive: tenacious lint on my jacket.
4. Tending to retain; retentive: a tenacious memory.
[From Latin tenâx, tenâc-, holding fast, from tenêre, to hold.]
- tena´ciously adverb
- tena´ciousness noun

The American Heritage® Dictionary of the English Language, Third Edition
copyright © 1992 by Houghton Mifflin Company. Electronic version licensed from
InfoSoft International, Inc. All rights reserved.

(Office 95 came with Bookshelf 95!)

 

[Pete_UK]

Yes, it's in my dictionary, too, though yours doesn't list "tenacity" -
this seems more natural to us Brits. I should have realised that the
Google Maestro would always be able to find a reference!! <bg>

Anyway, whichever, I think it is an attribute which most regular
posters exhibit.

Pete

I had to check my spelling first (just in case the CQC (Candian Quality Council)
was lurking!

tenacious

tenacious (te-nâ´shes) adjective
1. Holding or tending to hold persistently to something, such as a point of
view. See synonyms at STRONG.
2. Holding together firmly; cohesive: a tenacious material.
3. Clinging to another object or surface; adhesive: tenacious lint on my jacket.
4. Tending to retain; retentive: a tenacious memory.
[From Latin tenâx, tenâc-, holding fast, from tenêre, to hold.]
- tena´ciously adverb
- tena´ciousness noun

The American Heritage® Dictionary of the English Language, Third Edition
copyright © 1992 by Houghton Mifflin Company. Electronic version licensed from
InfoSoft International, Inc. All rights reserved.

(Office 95 came with Bookshelf 95!)

Isn't that tenacity? <bg>

Pete

 

[Dave Peterson]

Tenacity was in a different "branch" of the output. And this came from a
Bookshelf 95--a program that was included in office95--before MS came up with
Encarta (IIRC).

But heck, has anything changed in the world since 1995???

Yes, it's in my dictionary, too, though yours doesn't list "tenacity" -
this seems more natural to us Brits. I should have realised that the
Google Maestro would always be able to find a reference!! <bg>

Anyway, whichever, I think it is an attribute which most regular
posters exhibit.

Pete

I had to check my spelling first (just in case the CQC (Candian Quality Council)
was lurking!

tenacious

tenacious (te-nâ´shes) adjective
1. Holding or tending to hold persistently to something, such as a point of
view. See synonyms at STRONG.
2. Holding together firmly; cohesive: a tenacious material.
3. Clinging to another object or surface; adhesive: tenacious lint on my jacket.
4. Tending to retain; retentive: a tenacious memory.
[From Latin tenâx, tenâc-, holding fast, from tenêre, to hold.]
- tena´ciously adverb
- tena´ciousness noun

The American Heritage® Dictionary of the English Language, Third Edition
copyright © 1992 by Houghton Mifflin Company. Electronic version licensed from
InfoSoft International, Inc. All rights reserved.

(Office 95 came with Bookshelf 95!)