Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference function from an out of sort table
I'm looking for a reference function to pull the result from an out of sort
table (see table example below). The result should always come from the reference value LT A14. The table is always a whole number 0,1000,2000,3000, etc. I've tried the following functions, but they all require data in ascending order and/or exact match of the value in A14. =VLOOKUP($A$14,A$2:B$10,TRUE) =INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2) =LOOKUP($A$14,A$2:A$10,B$2:B$10) Table example: A B 2 1000 0.525 3 6000 0.946 4 3000 0.675 5 0 0.457 6 4000 0.746 7 2000 0.606 8 8000 1.29 9 5000 0.835 10 7000 1.09 If A14 = 3125, then result in B14 is 0.675. I don't want to have to use a complex IF/AND, like: =IF(AND($A$143000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"contin ue IF/AND") Thanks, Kevin |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort function for dates does not sort properly in Office 2007 Exc. | Excel Worksheet Functions | |||
Sort function on Pivot Table | Excel Worksheet Functions | |||
sort function to sort for bell curve | Excel Discussion (Misc queries) | |||
Sort as "reference column" | Excel Discussion (Misc queries) | |||
Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions |