I think VLOOKUP might work. In cell X1 try this: =VLOOKUP(B1,B$20:C$22,2,0) Copy down as needed; Adjust 2nd parameter (B$20:C$22) as needed.
On Mon, Nov 7, 2016 at 10:58 AM, Pertti Rönnberg <p...@elisanet.fi> wrote: > Best Spreadsheet Experts, > May I ask for help having a more elegant formula in LOCalc (v.5.0.3; win10) > > Description > I try to get a little extra to my pension savings by experimenting on the > share market and follow up the results using Calc (not a database). > I have a spreadsheet with two ranges – range(1) directly above range(2). > > r(1) specifies per row every specific purchase/sell event (date, company > name, amount, etc). > To calculate the actual "to-day"-result for this specific event (row) we > need the "to-day"-value for this company's share (in e.g. column X), which > value is taken from respective company's row in col.Z in r(2). > r(1) has yearly 100-130 rows (events) > > Range(2) lists each company named in the above Range(1) and has in col.Z > the corresponding share's "to-day"-value. These inserted share values > varies from one day to the next. > There are now 10-15 companies (rows) listed in r(2) – not in alphabetic > order. > r(2) sums the up-to-date result per company and the total result. > > Problem > The belowe example simplified to 5 events concerning 3 companies may > clearify the problem with the formula in X1 copied down col.X: > =if(B1=$B$20;$Z$20;if(B1=$B$21;$Z$21;(ifB1=$B$22;$Z$22;0))) > > As you can see: with 15 (or more) companies (and >100 events) the formula > will grow and become very long and sensitive for mistakes. > VLOOKUP does not seem to be an option, at least it requires a completely > new setup of the page. > > Range(1) - events > A B C…. X > 1. nokia 5,o > 2. kone 41,o > 3. nokia 5,o > 4. fiskars 12,o > 5. kone 41,o > > Range(2) - companies > A B C... Z > 20 nokia 5,o > 21 fiskars 12,o > 22 kone 41,o > > Any suggestion will be very interesting. > Thank you in advance > Pertti Rönnberg/Finland > > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-uns > ubscribe/ > 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 > -- To unsubscribe e-mail to: users+unsubscr...@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
