Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Next highest Value
I have a list of values...
B1 0 B2 238 B3 263 B4 510 B5 585 B6 774 B7 931 =INDEX(R22:R28,MATCH(R21,R21:R28,1)) works except when there is an exact match (i.e., 238) I would like a formula that will, when I enter a value in A1 that if not in the list will look up the next highest value. A1=238 Formula returns 238 A1=24 Formula returns 263 Help I cant see the forest for the trees.... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Next highest Value
Try this array formula** :
=MIN(IF(B1:B7=A1,B1:B7)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. If the lookup value is greater than the max value of the range the result will be 0. -- Biff Microsoft Excel MVP "Thanks" wrote in message ... I have a list of values... B1 0 B2 238 B3 263 B4 510 B5 585 B6 774 B7 931 =INDEX(R22:R28,MATCH(R21,R21:R28,1)) works except when there is an exact match (i.e., 238) I would like a formula that will, when I enter a value in A1 that if not in the list will look up the next highest value. A1=238 Formula returns 238 A1=24 Formula returns 263 Help I cant see the forest for the trees.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highest, Second Highest , Third Highest and so on | Excel Discussion (Misc queries) | |||
Display the Highest, Second Highest, Third Highest and so on... | Excel Discussion (Misc queries) | |||
2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates | Excel Worksheet Functions | |||
How can i get the 3 highest in a range | Excel Discussion (Misc queries) | |||
second highest value | Excel Discussion (Misc queries) |