Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 . |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup finds a blank, but returns a zero - HELP! | Excel Discussion (Misc queries) | |||
Vlookup Syntax Error | New Users to Excel | |||
Vlookup Question | Excel Discussion (Misc queries) | |||
Need help with modifying VLookUp | Excel Discussion (Misc queries) | |||
vlookup. | Excel Worksheet Functions |