Vlookup for a defined range (or a way to get around the insertion of new columns)

[MitchamWill]

Hey guys.

I've got a rather extensive set of asset worksheets. Essentially I have one main sheet with all the Asset data in it. this is around 350 rows with 30 columns.
my working sheets use the Asset ID column to suck asset info to other places and then the master list sucks back a project start and end date and a project ID associated with each asset.

I've hit a speed bump as I'm building this though in that I'm finding I need to add columns in my master list. This means that all the worksheets that reference this master list with a Vlookup become wrong as they reference the old master list column.

Is there a way to get around this by naming the ranges or something?

(The above explanation makes sense to me, but it might not to anyone else, so feel free to ask for more info)

Thanks,
Will

 

[etaf]

try using index match instead of vlookup
=INDEX(Sheet1!F:F,MATCH(Sheet2!F1,Sheet1!B:B,0))
so that will change the columns as you insert and delete

returns the value in sheet1 F (the data)
which matches a value on sheet 2 - F1
that is matched to column B in sheet1 (the data)