Forum Discussion
Excel is changing my vLookup formula
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.