Lookups that don't shift when new columns are inserted

[Monty85]

I don't seem to be having any luck trying to search for the solution I need so hoping for some help.

I have a spreadsheet I run that has various sheets which lookup data from a subset of columns from the source page.

When new data is received I insert a column at the start of this data which then shifts all my xlookup formulas across (following the original data that being looked up).

What im trying to do is not have this happen. So if my lookup formula is =XLOOKUP(A3,B:B,G:P) it will never change regardless of how many columns insert in-between Columns G and P.

 

[SanjayGMusafir]

The only solution to your problem is structured tables. That way references stick to a particular column header.

Named range can be another solution if you don't want a structured table (Just tested it, it works)

 

[Tetra201]

Try using

=XLOOKUP(A3,B:B,INDIRECT("G:P"))

 

[Peter_SSs]

If you have a lot of similar formulas in your worksheet then you may wish to avoid the volatile function INDIRECT. If so, you could consider this non-volatile option.

=XLOOKUP(A3,B:B,INDEX(A:P,0,7):INDEX(A:P,0,16))

 

[Monty85]

If you have a lot of similar formulas in your worksheet then you may wish to avoid the volatile function INDIRECT. If so, you could consider this non-volatile option.

=XLOOKUP(A3,B:B,INDEX(A:P,0,7):INDEX(A:P,0,16))

Yeah there is heaps of instances where I will need to use this so while the INDIRECT function did work its probably not useable in practice.

Your solution is more labour intensive to set up but if it doesn't impact the spreadsheet performance I think its worth it.

 

[Peter_SSs]

Cheers. Good luck with it.

 

[Monty85]

Cheers. Good luck with it.

Thanks. So far so good. I do have to wait for a calculation to take place when i initially insert the new column but that appears to be the only time. In theory I think this should mean that other users that access the spreadsheet won't have to deal with that - it will just be me when i update.

Thanks again - always a great help

 

[Peter_SSs]

Cheers. Glad to help.