Appl need that VLOOKUP will not handle

[Wayne G. Dengel]

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

 

[duane]

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

 

[Dave Peterson]

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.)

 

[Biff]

Hi!

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

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

Biff

 

[Dave Peterson]

And an excellent guess, too!

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]

Worked like a charm! Thank You!
Wayne

 

[Aladin Akyurek]

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.