Try this. =IF(B1="","",OFFSET($Z$20,MATCH(B1,$B$20:$B$35,0)-1,25))
-- Jim -----Original Message----- From: "Pertti Rönnberg" <[email protected]> To: "[email protected]" <[email protected]> Cc: "Pertti Rönnberg" <[email protected]> Sent: Mon, 07 Nov 2016 8:00 Subject: [libreoffice-users] Help with LOCalc formula 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: [email protected] 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 -- To unsubscribe e-mail to: [email protected] 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
