Prev: hyperlink problemNext: Look up table? From: AFSSkier on 1 Mar 2010 15:57 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 > 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 > First  |  Prev  |  Pages: 1 2 Prev: hyperlink problemNext: Look up table?