How do I lookup a value in a array that is not in ascending order
 

I have a array of numbers that I cannot sort in which I need to find a value
that is the next smallest (or exact) to the value I am looking for. Then I
want to return a corresponding value in another column, but in the same row.

I found if the numbers occurs twice, the LOOKUP commnad will yield the 2nd
incident of this number. The INDEX MATCH command will work, but only if the
number is an exact match, else same problems as lookup.

Hi!

Lookup Value in D1

Table in the range A1:B10

Array entered:

=IF(ISNA(VLOOKUP(D1,A1:B10,2,0)),INDEX(B1:B10,MATC H(MAX(IF(A1:A10<D1,A1:A10)),A1:A10,0)),VLOOKUP(D1, A1:B10,2,0))

If you have dupes......

Lookup value is 74. 74 does not exsist and there are dupe 73's with
different corresponding values to return, the first match will be returned.
If you want the nth instance............Good luck with that! SORT!

Biff

"John" wrote in message
...
I have a array of numbers that I cannot sort in which I need to find a
value
that is the next smallest (or exact) to the value I am looking for. Then I
want to return a corresponding value in another column, but in the same
row.

I found if the numbers occurs twice, the LOOKUP commnad will yield the 2nd
incident of this number. The INDEX MATCH command will work, but only if
the
number is an exact match, else same problems as lookup.

That amost does it - a big improvement! Really great.

But it is not quite working when I am looking for a number that does not
exist. For instance if I am looking for "12.0" in the 1st col, but 11.9 and
12.1 exist, would like to return the value in the second column that
corresponds to 11.9. I would also like to set up another formula that wuld
give me the value 11.9, so I know how far I am off my desired value.

Ideally, I want to return values from the row just prior to row that
contains the 1st value larger than my aim value. It would be like excel
looking at a colum for a value and either stopping when it finds that value,
or when a value exceeds the aim #, it goes back one row and then starts to
return values from that row - with values not necessarily in ascending order
in the columns.





"Biff" wrote:

Hi!

Lookup Value in D1

Table in the range A1:B10

Array entered:

=IF(ISNA(VLOOKUP(D1,A1:B10,2,0)),INDEX(B1:B10,MATC H(MAX(IF(A1:A10<D1,A1:A10)),A1:A10,0)),VLOOKUP(D1, A1:B10,2,0))

If you have dupes......

Lookup value is 74. 74 does not exsist and there are dupe 73's with
different corresponding values to return, the first match will be returned.
If you want the nth instance............Good luck with that! SORT!

Biff

"John" wrote in message
...
I have a array of numbers that I cannot sort in which I need to find a
value
that is the next smallest (or exact) to the value I am looking for. Then I
want to return a corresponding value in another column, but in the same
row.

I found if the numbers occurs twice, the LOOKUP commnad will yield the 2nd
incident of this number. The INDEX MATCH command will work, but only if
the
number is an exact match, else same problems as lookup.

Hi1

But it is not quite working when I am looking for a number that does not
exist. For instance if I am looking for "12.0" in the 1st col, but 11.9
and
12.1 exist, would like to return the value in the second column that
corresponds to 11.9.

Hmmm....

I don't see how that's possible because that is exactly what the formula is
designed to do.

Ideally, I want to return values from the row just prior to row that
contains the 1st value larger than my aim value.

That's not what you asked for in your original post!

I need to find a value that is the next smallest (or exact) to the value
I am looking for.

And that's what the formula does.

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!

Post an example of your data and explain what value you want returned.

I would also like to set up another formula that wuld give me the value
11.9, so I know how far I am off my desired value.

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

"John" wrote in message
...
That amost does it - a big improvement! Really great.

But it is not quite working when I am looking for a number that does not
exist. For instance if I am looking for "12.0" in the 1st col, but 11.9
and
12.1 exist, would like to return the value in the second column that
corresponds to 11.9. I would also like to set up another formula that wuld
give me the value 11.9, so I know how far I am off my desired value.

Ideally, I want to return values from the row just prior to row that
contains the 1st value larger than my aim value. It would be like excel
looking at a colum for a value and either stopping when it finds that
value,
or when a value exceeds the aim #, it goes back one row and then starts to
return values from that row - with values not necessarily in ascending
order
in the columns.





"Biff" wrote:

Hi!

Lookup Value in D1

Table in the range A1:B10

Array entered:

=IF(ISNA(VLOOKUP(D1,A1:B10,2,0)),INDEX(B1:B10,MATC H(MAX(IF(A1:A10<D1,A1:A10)),A1:A10,0)),VLOOKUP(D1, A1:B10,2,0))

If you have dupes......

Lookup value is 74. 74 does not exsist and there are dupe 73's with
different corresponding values to return, the first match will be
returned.
If you want the nth instance............Good luck with that! SORT!

Biff

"John" wrote in message
...
I have a array of numbers that I cannot sort in which I need to find a
value
that is the next smallest (or exact) to the value I am looking for.
Then I
want to return a corresponding value in another column, but in the same
row.

I found if the numbers occurs twice, the LOOKUP commnad will yield the
2nd
incident of this number. The INDEX MATCH command will work, but only
if
the
number is an exact match, else same problems as lookup.

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.

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.

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.