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