Bif,

Thank you very much. This really seems to do the job.

I am going through the formulas and trying to understand them nad not merely
use them. So far so good, except I don't undrestand the use off "" , in '
IF(D$1"",""...... '

in the If statement here -
=IF(D$1="","",IF(COUNTIF(A$1:A$10,D$1),VLOOKUP(D$1 ,A$1:B$10,2,0),"Not
listed"))

I thought the logical operator had to be some sort of value or text string.

Any books on Excel "prgramming" that you recommend?

Thanks again!

John







"Biff" wrote:

OK, try this:

Based on your sample of:

...........A..........B
1........1...........1
2........7...........2
3........8...........3
4........9...........4
5........10.........5
6........11.9......6
7........12.1......7
8........14.........8
9........15.........9
10......16.........10

D1 = lookup value

Formula in E1:

=IF(D$1="","",IF(COUNTIF(A$1:A$10,D$1),VLOOKUP(D$1 ,A$1:B$10,2,0),"Not
listed"))

If the lookup value is 10 and it exsists, the formula will return the
corresponding value from column B.
If the lookup value is 12.0 which does not exsist, the formula will return
"Not listed".

Formula in D2 (entered as an array**):

=IF(OR(D$1="",ISNUMBER(E$1)),"",IF(D$1<=MIN(A$1:A$ 10),"N/A",IF(E$1="Not
listed",MAX(IF(A$1:A$10<D$1,A$1:A$10)))))

Formula in D3 (entered as an array**)

=IF(OR(D$1="",ISNUMBER(E$1)),"",IF(D$1=MAX(A$1:A$ 10),"N/A",IF(E$1="Not
listed",MIN(IF(A$1:A$10D$1,A$1:A$10)))))

Formula in E2 and copied down to E3:

=IF(OR(D2="",ISTEXT(D2)),"",VLOOKUP(D2,A$1:B$10,2, 0))

** How to enter a formula as an array:

Type the formula and instead of hitting the ENTER key as you normally would,
depress and hold the CTRL key and the SHIFT key then hit ENTER. When done
properly Excel will enclose the formula in squiggly braces { }. You must
use the key combo to do this. You cannot just type the braces in.

How all those formulas work:

If you enter in a lookup value and it exsists then a simple lookup is
carried out and the result is displayed in cell E1. If the lookup value does
not exsist cell E1 will display "Not listed". Cell D2 will display the
largest value that is less than the lookup value. If there is no largest
value that is less than the lookup value then D2 will display "N/A". For
example: the lookup value is 12.0. It does not exsist so D2 will display
11.9 and cell E2 will perform a lookup on 11.9. If the lookup value was 0
(probably not likely but that's the range of the sample table) there is no
largest value less than 0 so D2 will display "N/A".

So, D2 returns the largest value that is less than the lookup value while D3
returns the smallest value that is greater than the lookup value. So, using
12.0 as the lookup value:

..............D.................E
1........12.0...........Not listed
2........11.9................6
3........12.1................7

Biff

"John" wrote in message
...
Figure out what you really want and then let me know. "We" should be able
to
find the next largest or the next lowest(as my formula already does) but
you
need to be clear about what you want!

OK, I have several columns of numbers. One column is torque values as I
tighten the fastener and thye do not go quite in ascending order. In
other
words, the general trend is ever increasing until the bolt strips, but
along
the way a torque value might be a little less than the one that proceeded
it
and a value may be duplicated several times. In another colum is clamping
force values that correspond to the torque value in the adjacent Torque
col.

What I am trying to accomplish: Obtain a given clamp load value for
predetermined torque value. So if a power tool was set to 12Nm for
instance,
it would keep tightening the bolt, until 12Nm was reached. Since the
exact
12 tq value that I am looking for may not be in the col., the actual tq
value
might be a little larger than what I am looking for. I would really like
to
be able to interpolate for the clamp load at exactly the tq. I am aiming
for.
So interpolation between the clamp load values that correspond to the
torque
values just past my aim point and the value in the prior (up 1) row.

However, typically even with out interpolation, obtaining the clampload
that
corresponds to torque value just prior to, or past the desired value will
suffice. (past preferred)

Sample of data for A1:A10
1
7
8
9
10
11.9
12.1
14
15
16

B1:b10
In b1:b10 will be misc values, typically asending, so lets use 1 thru 10





With the lookup value in D1

List of numbers is in the range of A1:A10

Enter this formula as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(D1="","",IF(COUNTIF(A1:A10,D1),D1&"
exsists",MAX(IF(A1:A10<D1,A1:A10))))

If the lookup value is 12.0 and it's in the list the formula will return:
12.0 exsists

If 12.0 does not exsist the formula will return the the largest value
that
is less than 12.0

Biff



I tried this, but if 12 did not exist, I got the largest (max) value in
the
list. I did not put the formula into an array, hoiwever, I wasn't quaite
sure why or how to do.

Thanks for your help so far - already this is a big improvment over what I
had to do! At the very least, I can now search and see is the number I
desire is in the list. If it is, it is now no problem to get the value in
the next col. that corresponds to the aim value in the 1st col.