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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference function from an out of sort table
On Mon, 1 Mar 2010 10:01:01 -0800, AFSSkier
wrote: 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 Try this modifying your second formula like this =INDEX(A$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10, 1),2) or, a little simpler, just =INDEX(B$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,0 )) Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference function from an out of sort table
If A14 = 3125, then result in B14 is 0.675.
What is the "rule" for finding the correct result? Is it: the closest value that is less than the lookup value? -- Biff Microsoft Excel MVP "AFSSkier" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference function from an out of sort table
Lars-Ã…ke,
Your suggestion for using the Floor function works perfectly. The following formulas work great for what I needed. =INDEX(B$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,0 )) or =VLOOKUP(FLOOR($A$14,1000),$A$2:$B$10,2,FALSE) -- Thanks, Kevin "Lars-Ã…ke Aspelin" wrote: On Mon, 1 Mar 2010 10:01:01 -0800, AFSSkier wrote: 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 Try this modifying your second formula like this =INDEX(A$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10, 1),2) or, a little simpler, just =INDEX(B$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,0 )) Hope this helps / Lars-Ã…ke . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference function from an out of sort table
Although this can be done (of course), you (or your principal) are making it
difficult. Why not sort the table (ascending) so you can use standard VLOOKUP functionality? If that is not possible, please give some more information about the (type of) problem you are trying to solve; we are just not prepared to believe things should be that difficult.:-) -- Kind regards, Niek Otten Microsoft MVP - Excel "AFSSkier" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference function from an out of sort table
I would be a lot easier if the table was not shared or if it was a perfect
world. But as you know, we're all in end-user hell & it's not a perfect world. The table is imported in ascending order. But the end-users are able to sort as they need it printed. I know my posted example doesn't reflect this, I simplified it for illustration. -- Thanks, Kevin "Niek Otten" wrote: Although this can be done (of course), you (or your principal) are making it difficult. Why not sort the table (ascending) so you can use standard VLOOKUP functionality? If that is not possible, please give some more information about the (type of) problem you are trying to solve; we are just not prepared to believe things should be that difficult.:-) -- Kind regards, Niek Otten Microsoft MVP - Excel "AFSSkier" wrote in message ... 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 |
Reply |
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 |