Appl need that VLOOKUP will not handle
 

I need means to locate the nearest nbr in column of nbrs that is GREATER
than the source nbr. (Did I say that right?) VLOOKUP finds the nearest
LOWEST.

nbr in question: 6.2
list of nbrs:
4.2
5.6
7.9
10.4
13.5
15.0

Entering 6.2, I would like to find 7.9 (NOT 5.6). Please how do I do this?

I am not experienced at Visual Basic, thus please need a detailed answer.

Thank You!

Wayne

I should add this will return the same number if there is an exact match
in the table, else it returns the next larger number


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=275965

This seemed to work for me. I put the data in Sheet1, A1:A6 and

=IF(ISNUMBER(MATCH(A1,Sheet1!A1:A6,0)),
INDEX(Sheet1!A1:A6,MATCH(A1,Sheet1!A1:A6,0)),
INDEX(Sheet1!A1:A6,MATCH(A1,Sheet1!A1:A6,1)+1))
(all one cell)

If it's a match, use it. If it's not a match, then find the lower number's
position and add 1--and return that one.

(I guessed an exact match should return that exact value.)

"Wayne G. Dengel" wrote:

I need means to locate the nearest nbr in column of nbrs that is GREATER
than the source nbr. (Did I say that right?) VLOOKUP finds the nearest
LOWEST.

nbr in question: 6.2
list of nbrs:
4.2
5.6
7.9
10.4
13.5
15.0

Entering 6.2, I would like to find 7.9 (NOT 5.6). Please how do I do this?

I am not experienced at Visual Basic, thus please need a detailed answer.

Thank You!

Wayne

--

Dave Peterson

Hi!

If your list of numbers is sorted ascending like your
example:

=INDEX(A3:A8,MATCH(A1,A3:A8,1)+1)

Biff

-----Original Message-----
I need means to locate the nearest nbr in column of nbrs
that is GREATER
than the source nbr. (Did I say that right?) VLOOKUP
finds the nearest
LOWEST.

nbr in question: 6.2
list of nbrs:
4.2
5.6
7.9
10.4
13.5
15.0

Entering 6.2, I would like to find 7.9 (NOT 5.6).
Please how do I do this?

I am not experienced at Visual Basic, thus please need a
detailed answer.

Thank You!

Wayne


.

And an excellent guess, too!



Dave Peterson wrote:

This seemed to work for me. I put the data in Sheet1, A1:A6 and

=IF(ISNUMBER(MATCH(A1,Sheet1!A1:A6,0)),
INDEX(Sheet1!A1:A6,MATCH(A1,Sheet1!A1:A6,0)),
INDEX(Sheet1!A1:A6,MATCH(A1,Sheet1!A1:A6,1)+1))
(all one cell)

If it's a match, use it. If it's not a match, then find the lower number's
position and add 1--and return that one.

(I guessed an exact match should return that exact value.)

"Wayne G. Dengel" wrote:

I need means to locate the nearest nbr in column of nbrs that is GREATER
than the source nbr. (Did I say that right?) VLOOKUP finds the nearest
LOWEST.

nbr in question: 6.2
list of nbrs:
4.2
5.6
7.9
10.4
13.5
15.0

Entering 6.2, I would like to find 7.9 (NOT 5.6). Please how do I do this?

I am not experienced at Visual Basic, thus please need a detailed answer.

Thank You!

Wayne

--

Dave Peterson


--

Dave Peterson

Worked like a charm! Thank You!
Wayne

"Wayne G. Dengel" wrote in message
news:V3gjd.2181$bH2.536@trnddc09...
I need means to locate the nearest nbr in column of nbrs that is GREATER
than the source nbr. (Did I say that right?) VLOOKUP finds the nearest
LOWEST.

nbr in question: 6.2
list of nbrs:
4.2
5.6
7.9
10.4
13.5
15.0

Entering 6.2, I would like to find 7.9 (NOT 5.6). Please how do I do
this?

I am not experienced at Visual Basic, thus please need a detailed answer.

Thank You!

Wayne

If the numbers are in ascending order as your sample suggests...

=INDEX($A$2:$A$7,MATCH(C1,$A$2:$A$7,1)+(LOOKUP(C1, $A$2:$A$7)<C1))

where A2:A7 houses the numbers and C1 the value to look up.

Wayne G. Dengel Wrote:
I need means to locate the nearest nbr in column of nbrs that is
GREATER
than the source nbr. (Did I say that right?) VLOOKUP finds the
nearest
LOWEST.

nbr in question: 6.2
list of nbrs:
4.2
5.6
7.9
10.4
13.5
15.0

Entering 6.2, I would like to find 7.9 (NOT 5.6). Please how do I do
this?

I am not experienced at Visual Basic, thus please need a detailed
answer.

Thank You!

Wayne


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=275965