Excel is changing my vLookup formula

%3CLINGO-SUB%20id%3D%22lingo-sub-953287%22%20slang%3D%22en-US%22%3EExcel%20is%20changing%20my%20vLookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-953287%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20having%20a%20really%20odd%20issue%20with%20an%20Excel%20report%20I%20have%20created%2C%20and%20the%20vlookup%20function.%20Some%20background%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20pulled%20in%20multiple%20live%20reports%20from%20an%20online%20source%20through%20'queries%20and%20connections'%2C%20and%20they%20are%20all%20pulling%20in%20alright%2C%20refreshing%20fine%2C%20all%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20bring%20all%20this%20data%20together%2C%20one%20of%20the%20reports%20that%20is%20being%20pulled%20through%20the%20queries%20is%20only%20pulling%20one%20column%2C%20a%20list%20of%20values.%20These%20live%20values%20are%20being%20used%20to%20conduct%20a%20vlookup%20on%20the%20rest%20of%20the%20sheets%20of%20live%20reports%2C%20so%20that%20in%20the%20end%2C%20I%20have%20a%20single%20visible%20sheet%2C%20with%20one%20live%20column%20updating%20all%20the%20rest%20of%20the%20columns.%20This%20works%20beautifully%2C%20and%20all%20of%20the%20information%20in%20the%20columns%20is%20updating%20correctly%20when%20refresh%20all%20is%20hit.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%2C%20until%20new%20values%20are%20added%20to%20column%20A.%20I%20have%20the%20entire%20sheet%20set%20up%20to%20accommodate%20up%20to%201600%20values%20in%20column%20A%2C%20with%20the%20formulas%20in%20all%20the%20empty%20rows%20just%20chilling%20until%20a%20value%20appears%20in%20column%20A.%20The%20problem%20is%20that%20when%20a%20new%20value%20DOES%20appear%2C%20instead%20of%20leaving%20my%20formulas%20as%20they%20are%2C%20Excel%20has%20decided%20to%20change%20the%20very%20last%20row%20that%20previously%20had%20information%20displayed%20correctly.%20Instead%20of%20referencing%20the%20cell%20directly%20next%20to%20it%2C%20it%20jumps%20the%20vlookup%20reference%20cell%20down%2C%20the%20exact%20number%20of%20lines%20that%20have%20been%20added%2C%20so%20that%20the%20row%20shows%20errors.%20The%20rest%20of%20the%20rows%20below%20then%20follow%20this%20new%20number%20sequence%2C%20all%20referencing%20empty%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20I%20think%20it%20is%20worth%20noting%20that%20I%20have%20already%20made%20some%20major%20changes%20trying%20to%20fix%20this%20issue.%20Originally%2C%20I%20had%20all%20of%20the%20live%20reports%20pulling%20in%20through%20queries%2C%20separate%20from%20this%20main%20sheet%2C%20and%20I%20was%20using%20an%20equals%20function%20to%20bring%20in%20Column%20A%E2%80%99s%20values.%20The%20same%20type%20of%20issue%20was%20occurring%20though%3B%20when%20new%20values%20were%20added%20to%20the%20live%20reports%2C%20the%20equals%20function%20in%20column%20A%20would%20break%20at%20the%20last%20line%2C%20and%20cause%20errors.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyone%20who%20has%20any%20notion%20of%20what%20I%20can%20do%20is%20welcome%20to%20help%3B%20I%20have%20already%20tried%3A%20Locking%2Fprotecting%20the%20sheet%20(which%20then%20prevents%20refreshes%20from%20happening%20until%20the%20sheet%20is%20unprotected%20and%20makes%20no%20difference)%2C%20absoluting%20the%20reference%20cell%20with%20%E2%80%98%24%E2%80%99s%20(which%20doesn%E2%80%99t%20make%20a%20difference%20either%2C%20and%20insultingly%20enough%2C%20excel%20changes%20my%20formula%20AND%20keeps%20the%20%E2%80%98%24%E2%80%99%20sign%2C%20as%20if%20that%E2%80%99s%20what%20it%20always%20said)%2C%20and%20Googling%20it%20(which%20told%20me%20how%20to%20protect%20locked%20cells%2C%20which%20I%20already%20knew%2C%20and%20would%20only%20be%20helpful%20if%20another%20user%20was%20making%20these%20changes)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20and%20thank%20you!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-953287%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-955023%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20is%20changing%20my%20vLookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-955023%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F433480%22%20target%3D%22_blank%22%3E%40Jtravis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20the%20use%20of%20explicit%20references%20in%20the%20VLOOKUP%20(e.g.%20A2).%26nbsp%3B%20The%20same%20thing%20were%20to%20happen%20if%20you%20have%20a%20range%20of%20data%20which%20includes%20a%20COUNTIF%2C%20in%20one%20of%20its%20columns%2C%20for%20example.%26nbsp%3B%20A%20data%20sort%20is%26nbsp%3B%20performed%20and%20then%20it%20shuffles%20the%20criteria%20used%20in%20the%20COUNTIF.%20In%20other%20words%2C%20it%20forgot%20which%20row%20it%20belonged%20to.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EWithout%20seeing%20the%20workbook%20my%20guess%20is%20you're%20not%20using%20table%20references.%26nbsp%3B%20Those%20queries%20will%20come%20in%20as%20tables%20so%20if%20you%20VLOOKUP%20off%20the%20query%20it%20should%20place%20a%20table%20reference%20(For%20example%2C%20if%20your%20column%20is%20called%20%22ID%22%20and%20you're%20doing%20a%20VLOOKUP%20off%20the%20ID%20in%20that%20row%20the%20table%20reference%20is%3A%20%5B%40ID%5D%20)%20in%20the%20VLOOKUP%20instead%20of%20the%20explicit%20cell%20reference.%26nbsp%3B%20You%20could%20also%20use%20an%20INDIRECT%20or%20OFFSET%20to%20refer%20to%20the%20lookup%20value%20but%20the%20table%20makes%20it%20easy.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

I am having a really odd issue with an Excel report I have created, and the vlookup function. Some background:

 

I have pulled in multiple live reports from an online source through 'queries and connections', and they are all pulling in alright, refreshing fine, all that.

 

To bring all this data together, one of the reports that is being pulled through the queries is only pulling one column, a list of values. These live values are being used to conduct a vlookup on the rest of the sheets of live reports, so that in the end, I have a single visible sheet, with one live column updating all the rest of the columns. This works beautifully, and all of the information in the columns is updating correctly when refresh all is hit.  

 

That is, until new values are added to column A. I have the entire sheet set up to accommodate up to 1600 values in column A, with the formulas in all the empty rows just chilling until a value appears in column A. The problem is that when a new value DOES appear, instead of leaving my formulas as they are, Excel has decided to change the very last row that previously had information displayed correctly. Instead of referencing the cell directly next to it, it jumps the vlookup reference cell down, the exact number of lines that have been added, so that the row shows errors. The rest of the rows below then follow this new number sequence, all referencing empty cells.

 

Now I think it is worth noting that I have already made some major changes trying to fix this issue. Originally, I had all of the live reports pulling in through queries, separate from this main sheet, and I was using an equals function to bring in Column A’s values. The same type of issue was occurring though; when new values were added to the live reports, the equals function in column A would break at the last line, and cause errors.

 

Anyone who has any notion of what I can do is welcome to help; I have already tried: Locking/protecting the sheet (which then prevents refreshes from happening until the sheet is unprotected and makes no difference), absoluting the reference cell with ‘$’s (which doesn’t make a difference either, and insultingly enough, excel changes my formula AND keeps the ‘$’ sign, as if that’s what it always said), and Googling it (which told me how to protect locked cells, which I already knew, and would only be helpful if another user was making these changes)

 

Please and thank you!!!

1 Reply

@Jtravis 

The problem is the use of explicit references in the VLOOKUP (e.g. A2).  The same thing were to happen if you have a range of data which includes a COUNTIF, in one of its columns, for example.  A data sort is  performed and then it shuffles the criteria used in the COUNTIF. In other words, it forgot which row it belonged to.


Without seeing the workbook my guess is you're not using table references.  Those queries will come in as tables so if you VLOOKUP off the query it should place a table reference (For example, if your column is called "ID" and you're doing a VLOOKUP off the ID in that row the table reference is: [@ID] ) in the VLOOKUP instead of the explicit cell reference.  You could also use an INDIRECT or OFFSET to refer to the lookup value but the table makes it easy.