Re: [libreoffice-users] calc

At 23:33 05/06/2024 -0400, James Lockie wrote:
> Say I have column A with the numbers 1, 2, 3:
> A1 (1)
> A2 (2)
> A3 (3)
> and column B with the numbers 11, 22, 33:
> B1 (11)
> B2 (22)
> B3 (33)
>
> Column A Row 4 contains a reference to one of row 1, 2, or 3 (I 
> switch which row is referenced so I can see the effects of different choices).

It's not clear what you mean by a "reference". I'm guessing you mean 
that A4 contains just one of the numbers (1, 2, or 3) that appear in 
your list, not a true reference, such as "=A1"? Otherwise the problem 
would be trivial.

> Is there a calc function which can find which row I chose so I 
> automatically populate row 4, column B?

Yup. In B4, enter:
=VLOOKUP(A4;A1:B3;2;FALSE)

VLOOKUP() looks for the value in A4 in the first column of the array 
A1:B3 (i.e. column A) and returns as its result the corresponding 
value in column 2 of the array (i.e. column B). The FALSE parameter 
indicates that the values in column A are not necessarily in 
ascending order. If you know they are in such order, you can use TRUE 
(or omit that parameter); that allows the program to calculate faster 
but also means that a value may be returned even if an exact match 
does not exist - which I'm guessing you wouldn't want.

> Row 4, column A matches one of the rows 1-3...

You may want to look into the detail of how to handle cases where the 
value in A4 either does not appear in the list or even appears more than once.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy