Re: [libreoffice-users] Problems using VLOOKUP in Libreoffice Calc

At 16:18 28/08/2011 -0700, M S Fossen wrote:
> I have two columns set up like so:
>
> a |  1
> b |  2
> c |  3
>
> I want to be able to use a number from column B and have the text 
> from column A displayed.  But when I put {=vlookup(B4,A1:B3,1,0)} in 
> cell A4, and {=max(B1:B3)} in B4
> (minus the brackets for both), I get back, correctly, 3 for the max, 
> but #N/A for the VLOOKUP.  Putting in the letters a, b, or c in B4 
> correctly returns the number it's
> next to, so it seems like only the first column in the array is searched.

That's correct: VLOOKUP() searches the first column of the array; its 
result can - and generally does - come from later columns.

> Is it possible to flip the way the columns are searched?

Not that I can see.  Two workarounds:

o  Reverse the columns of your array.

o  Put =A1 in C1 and drag it down column C.  You now have a dynamic 
copy of column A in column C.  Change your formula to 
=VLOOKUP(B4,B1:C3,2,0).  You are now using columns B and C to do the 
lookup and using the second column (C) to provide the returned 
value.  You can hide column C if you prefer - without upsetting the result.

I trust this helps.

Brian Barker


--
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