Reference function from an out of sort table
in [Functions]
|
Prev: hyperlink problem
Next: Look up table?
From: AFSSkier on 1 Mar 2010 13:01 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$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue IF/AND") Thanks, Kevin
From: Lars-�ke Aspelin on 1 Mar 2010 13:15 On Mon, 1 Mar 2010 10:01:01 -0800, AFSSkier <AFSSkier(a)discussions.microsoft.com> 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$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue >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
From: T. Valko on 1 Mar 2010 13:17 >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" <AFSSkier(a)discussions.microsoft.com> wrote in message news:0CF9A182-3DAD-4518-A6A4-66A3F409CE8E(a)microsoft.com... > 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$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue > IF/AND") > > Thanks, Kevin
From: AFSSkier on 1 Mar 2010 15:15 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 > <AFSSkier(a)discussions.microsoft.com> 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$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue > >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 > . >
From: Niek Otten on 1 Mar 2010 15:18
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" <AFSSkier(a)discussions.microsoft.com> wrote in message news:0CF9A182-3DAD-4518-A6A4-66A3F409CE8E(a)microsoft.com... > 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$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue > IF/AND") > > Thanks, Kevin |