Prev: hyperlink problem
Next: 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" <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
>
First  |  Prev  | 
Pages: 1 2
Prev: hyperlink problem
Next: Look up table?