Re: [libreoffice-users] VLOOKUP for Numbers

I too hadn't realized the value of the last parameter being 1 (meaning 
it takes the next number below).  I had trouble getting the formula to 
work until I realized I had pasted the array from the email and some of 
the numbers were formatted as text, others not, so I was getting almost 
all #N/A.  Then I fixed it and it works. Thanks Brian.  Excellent little 
exercise for me to learn more about VLOOKUP.

Carl



On 3/26/13 12:48 AM, Brian Barker wrote:
> At 21:17 25/03/2013 -0700, Jason C. Wells wrote:
> > OK. Take two. Since VLOOKUP for numbers with an inequality is not 
> > meaningful, ...
>
> It will do what you require, I think.
>
> > ... what I precisely hope to do is this. I hope that the preformatted 
> > columns survive the mailer software.
> >
> > Give the following columns:
> >
> > A       B       C       D
> > ---     ---     ---     ---
> > 1.1     eqn     3       0.10
> > 1.3             6       0.12
> > 1.5             10      0.15
> > 1.7             18      0.18
> > 2               30      0.21
> > 2.2             50      0.25
> > 2.7             80      0.30
> > 3.2             120     0.35
> > 3.7             180     0.40
> > 4.3
> > 4.8
> > 5.3
> > 6.4
> > 7.4
> > 8.4
> >
> > Compare cells in column A such that:
> >
> >   0  > A1 <= C1
> >   C1 > A1 <= C2
> >   C2 > A1 <= C3
> >   ...
>
> I'm hoping you mean:
>   0  < A1 <= C1
>   C1 < A1 <= C2
>   C2 < A1 <= C3
>
> > is true. Then return the value of D for the appropriate row. Repeat 
> > for all values of A. When done, the length of column B will be equal 
> > to the length of column A. B will contain values of column D. Both C 
> > and A will always be sorted. (Although it would be nice to be able to 
> > do this with A being random numbers.)
> >
> > It seems like I need an array equation, but the array A is not equal 
> > in size to the array in C.  Simple string matching of VLOOKUP is 
> > inadequate to the task at hand.
>
> You are still talking of strings with respect to VLOOKUP(), but it 
> will cope with your numbers too!  And it will do your job ...
>
> You have missed the important value zero from your column C, so please 
> insert it in C1, moving the other values down.  *Do not* move the 
> values in column D, so that the first value, 0.10, is now against zero 
> in column C, 0.12 is against 3, and so on.  (It seems that you don't 
> need the maximum value 180 now in C10, in fact.)
>
> In B1, enter:
> =VLOOKUP(A1;C$1:D$9;2)
> and fill down column B.
>
> Voilà!
>
> This even works with column A values not being sorted, as you have 
> asked.  All that is required is that column C should be sorted - and 
> that is no problem, of course.
>
> I trust this helps.
>
> Brian Barker

--

Carl Paulsen

8 Hamilton Street

Dover, NH 03820

(603) 749-2310


--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted