VLOOKUP looking outside of my defined array

English
,
#1

I’ve only deployed VLOOKUP on one file and it’s been working fine using this guide until just now. My calculation appears in every cell in Column U and is this: =VLOOKUP(H777,V:W,1,0) where column H contains a unique customer identifier (account#) in each row for hundreds of rows, and anywhere in Column V is where I’d like the calculation to look for that identifier. Column V is where I temporarily paste unique identifiers of customers who made a transaction at our website. If it does not find the value from Column H in Column V, I want it to return an error. So, after pasting the account numbers of customers who made a transaction into Column V, I sort columns A:U by Column U – which takes waaaaaaay too long – but if I’m patient it brings to the top all the rows of customers who made a transaction – where I can code/remove them. Except for the exceptionally bad lag on the Sort, that has worked.

But now even though Column V (and W) are completely empty, four lines of column U where the calculations live are returning a value, and instead of an error the rest of the cells are returning a 0. Now, I can live with the 0–no one’s account number is 0 so that won’t be a problem. But my calculation says “look in V:W” and yet V:W is completely blank and the calculation – on only four rows!? – is finding a value that can be found ONLY in column H. That is to say, when I ctrl+F on the value of the cell in column V that has been returned, the only place it finds that value is in column H. I just double checked my calculations and they indeed indicate the array is V:W.

How did I break it? Does anyone have a completely better and different way of accomplishing this, as I do not love this solution. Please help.BROKEN CALCULATIONS - data removed.ods

#2

Column V is where I temporarily paste unique identifiers of customers who made a transaction at our website

Without the details of a real file hard to tell, but the temporarily makes my gut feel that you might want to check for Advanced Filters - see LibreOffice Help - Filter: Applying Advanced Filters in combination with Named Ranges and Range Options: [x] Filter

#3

I’ve already been down that road: Remove duplicates -- "filter" is hard to understand

In the end, no one stood up for filter as the tool for my task.

#4

Why do you ask for alternatives, if you are in fact bound to specific solution. Bye…

#5

@default_abuser, Just share a file with one row with fictitious data. I adapt the formula, and you only need to copy and paste the formula in the right place in a copy of your document to give it a try.

(Oct 22 '20, Remove duplicates -- "filter" is hard to understand)

#6

I’ve uploaded it. At this point, I’m just trying to figure out what went wrong so I can avoid it. In this case, I had a backup where the calculations were not (yet) broken, so all is good. But I may lose a lot of work someday if I notice the calculations are broken and now need to revert to an old backup.

#7

@default_abuser, I am seen your file. What is the data that you paste in columns V and W? AcctID
and …

#8

Just account IDs and I actually paste them only into V. I have no use for column W, an array simply needs to be at least two columns for some reason.

#9

@anon73440385 Your solution requires me to abandon entirely what I’ve been working on and learn entirely new things. The best solution would require no learning–I would simply just go do it with an easy click-by-click guide (that you did not provide). The current working solution – using VLOOKUP and Sort – was entirely new to me not long ago, but now I’m well on the way to mastery. Should I master it and judge it inadequate (and the lag may doom it to being deemed inadequate), I will move onto suggestions such as yours. But learning these new techniques takes me several days, so frankly I’m not super capable of trying more than one at a time unless they are so easy they do not require any new learning on my part and you can simply give me click-by-click instructions. Another suggestion I’ve received and am considering is doing this entire task in Base, but as that would also require a bunch of new learning , I haven’t begun to explore that fully yet either. Thanks for your input

#10

  • Delete old data from column V

  • Paste IDs with new transactions in column V (don’t worry with order or blank cells)

  • Populate U with the formula in U2 (=IFERROR(VLOOKUP(H2;V:V;1;0);"--")): copy U2, press Ctrl+Shift+End and Shift+Left Arrow, and paste

  • Recalculate: press F9 (Ctrl+Shift+F9 if in doubts)

  • Select range from A2 to Ux (being x the last row): select A2 and press Ctrl+Shift+End and Shift+Left Arrow

  • Sort by column U

  • Remove rows if ID number is seen in column U

See sample file.

Tested with LibreOffice 6.4.7.2 (x86); OS: Windows 6.1.

Add Answer is reserved for solutions.

Press edit below your question if you want to add more information; also can comment an answer.

Check the mark (Answer mark → Correct answer mark) to the left of the answer that solves your question.

#11

Just CTRL+Shift+F9 solved the problem – i feel entirely stupid. Some other helper on this board had instructed me to turn off auto-calculate trying to relieve me of the huge lag that ocurrs when I sort the column of calculations (it didn’t help anyway) and I guess I didn’t turn auto-calculate back on.

#12

Great that your problem is solved! Regards.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.