Forum Discussion
Xlookup shifts if I add a column to the query
Yes I’m using proper column names, I just used numbers to help understand the order but that’s is exactly what’s happening. If my column names are, Apple, Banana, cranberry, if my XLOOKUP is referencing “Cranberry” (the 3rd one) but if my column order is now Apple, GRAPE, banana, cranberry, my reference automatically switches to “Banana” (the 3rd one). Is there a setting I accidentally switched? Literally everyone’s response online is “that’s shouldn’t happen” but it is happening and I’m gonna lose my mind . It’s also effecting FILTER commands and anything that’s is referenced like “Table_Name[Column Name]”, they all shift over.
References within a table are properly aligned but anything that’s a cross reference get shifted over by the number of new columns there are.
Another example I typed up for Microsoft support.
My table name is "Tracking_Table" and in order, my columns are named
Client Name, Location, Ratio, Notes.
In another table I have
=XLOOKUP("John Smith", Tracking_Table[Client Name], Tracking_Table[Notes],"").
And then in power query, a new column is added in between Location and Ratio called Edits. My new order of columns is Client Name, Location, Edits, Ratio, Notes.
When the query refreshes, my formula changes to =XLOOKUP("John Smith", Tracking_Table[Client Name], Tracking_Table[Ratio],"")
This is just one of the many examples in my workbook. Anything to the right of the new columns will be offset and because there are so many files and so many lookups, there is no way to just put all new columns to the right because there will always be more columns to the right.