Ask Your Question

VLOOKUP looking outside of my defined array

asked 2020-11-05 23:24:00 +0200

default_abuser gravatar image

updated 2020-11-09 16:55:33 +0200

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.C:\fakepath\BROKEN CALCULATIONS - data removed.ods

edit retag flag offensive close merge delete


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

Opaque gravatar imageOpaque ( 2020-11-05 23:36:37 +0200 )edit

I've already been down that road:

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

default_abuser gravatar imagedefault_abuser ( 2020-11-05 23:45:56 +0200 )edit

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

Opaque gravatar imageOpaque ( 2020-11-06 16:34:42 +0200 )edit

@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,

LeroyG gravatar imageLeroyG ( 2020-11-06 20:52:21 +0200 )edit

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.

default_abuser gravatar imagedefault_abuser ( 2020-11-09 16:56:33 +0200 )edit

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

LeroyG gravatar imageLeroyG ( 2020-11-09 17:11:29 +0200 )edit

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.

default_abuser gravatar imagedefault_abuser ( 2020-11-09 17:14:24 +0200 )edit

@Opaque 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 ...(more)

default_abuser gravatar imagedefault_abuser ( 2020-11-09 17:21:36 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-11-09 17:58:45 +0200

LeroyG gravatar image
  • 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 (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 markCorrect answer mark) to the left of the answer that solves your question.

edit flag offensive delete link more


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.

default_abuser gravatar imagedefault_abuser ( 2020-11-09 18:10:56 +0200 )edit

Great that your problem is solved! Regards.

LeroyG gravatar imageLeroyG ( 2020-11-09 18:16:38 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-11-05 23:24:00 +0200

Seen: 58 times

Last updated: Nov 09 '20