| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup number above value in a list
I need two formulas to lookup the numbers above and below a certain value in
a list. I know how to get the one below, but can't figure out the higher number. Example: A 1 97745 2 98913 3 100067 4 101248 5 102416 6 7 =VLOOKUP(A8,A1:A5,1) 8 100000 9 =??? A7 results in 98913 A9 results in 100067 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup number above value in a list
try:
=INDEX(A1:A5,MATCH(A8,A1:A5,1)+1) HOWVER, you need to cater for situation (if it can exist) that your number is than last in list OR < first in list. HTH "Sloth" wrote: I need two formulas to lookup the numbers above and below a certain value in a list. I know how to get the one below, but can't figure out the higher number. Example: A 1 97745 2 98913 3 100067 4 101248 5 102416 6 7 =VLOOKUP(A8,A1:A5,1) 8 100000 9 =??? A7 results in 98913 A9 results in 100067 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup number above value in a list
thanks. I like that a whole lot better than the INDIRECT formula I was using.
"Toppers" wrote: try: =INDEX(A1:A5,MATCH(A8,A1:A5,1)+1) HOWVER, you need to cater for situation (if it can exist) that your number is than last in list OR < first in list. HTH "Sloth" wrote: I need two formulas to lookup the numbers above and below a certain value in a list. I know how to get the one below, but can't figure out the higher number. Example: A 1 97745 2 98913 3 100067 4 101248 5 102416 6 7 =VLOOKUP(A8,A1:A5,1) 8 100000 9 =??? A7 results in 98913 A9 results in 100067 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Lookup on an expanding list | Excel Worksheet Functions | |||
| Vlookup of list with text and number | Excel Discussion (Misc queries) | |||
| Select every 10th number in a list | Excel Discussion (Misc queries) | |||
| Creating an invoice with a lookup list | Excel Discussion (Misc queries) | |||
| Excel formula divide list of #s by N to get a number above 40 | Excel Discussion (Misc queries) | |||
Linear Mode
