Description: When a VLOOKUP() or HLOOKUP() array is modified by the insertion or deletion of rows or columns the target reference pointing into the array is not updated with the new row or column reference. That is to say if the formula is VLOOKUP(E1:H12;"Row4";3) and a new column is inserted before the third column"G" -this being the target "3" then the contents of column G are moved to column F or "4" in targeting terms but the formula continues to point at column 3 which is now blank. that is to say Formula(ic) references to coordinates in a VLOOKUP() or HLOOKUP table are not updated to the new array coordinates if the target array is modified after the formula has been created. YES - MENU>TOOLS>OPTIONS>CALC>GENERAL>INPUT SETTINGS expand references when columns/rows are inserted has been selected/activated. Steps to Reproduce: Using the attached CALC Observe the formulae and values in A4:A5 Right-click column header "G" and insert one column before Observe the formula and value in A4 Right-click row header "8" and delete one row Observe the formula and value in A5 The impact is also observable when columns are deleted and rows are inserted Pre-naming the array and referring to the name in the formula does not remediate. Actual Results: Formula is not updated to reflect the new coordinates in the Target array Expected Results: Formula should be updated Reproducible: Always User Profile Reset: No Additional Info: Version: 7.4.7.2 (x64) / LibreOffice Community Build ID: 723314e595e8007d3cf785c16538505a1c878ca5 CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded
Created attachment 189027 [details] Simple Example CALC
You have defined the third parameter with a value, so it doesn't change, no matter what you do inserting/deleting rows/columns. In S4 use a formula like =VLOOKUP(A1;E2:H13;COLUMNS(E1:H1)) In the future, please go to ask.libreoffice.org and ask your question before come here. So you can confirm it is really a bug.