Inserted Columns into VLOOKUP Tables

[RJB]

So I have a whole bunch of tables that are summarized in a few
presentation charts by using massive amounts of VLOOKUPs.

It's the 11th hour - I'm taking the charts to the printer.

I've discovered that if I insert a column to the ORIGINAL chart, the
VLOOKUP does not float...

In other words:

On "Original Chart", I have columns A-C, Customer, Region, Sales.
On "Presentation Chart", I have VLOOKUP (A1, Original Chart, 3,
false). So that returns "Sales".

Now, we've added stuff to "Original Chart": Now it's, A-E, Customer,
Sales Manager, Region, Phone Number, Sales.

Now, when I load "Presentation Chart", the lookup returns "Region"...
It didn't 'push' the lookup to float with "Sales".

Is there any way to resolve? They're not all so simple, some are
"Margins" and "Revised Margins", so a quick scan won't pass the BS
test to figure out if/where my data went funny.

Uh-oh...

 

[Pete_UK]

Your lookup formula is asking for data to be returned from column 3 of
the table, and this will not change when you insert columns into the
table. In your example you want that 3 to become a 5, so the easiest
way is to highlight the cells with the VLOOKUP formula in and do Edit
| Replace (or CTRL-H), and then:

Find What: , 3,
Replace with: , 5,

Click Replace All

By having those commas either side of the number, it avoids A3 being
changed to A5. I've put a space in as that is how you show it, but you
might have to re-apply it without the space.

Hope this helps.

Pete

 

[RJB]

Thanks, my concern is where I DON'T know for certain changes have been
made over the life of the project. I guess I'm hoping someone posts
the "Don't you know about VLOOKUPAUDIT function" or something that
will help me trace through all the files and double-check data
integrity at this point.

You put too many people on a project, you're up all night cleaning
this stuff up. You don't put enough people on, you're up all night
doing the work.

I should have learned how to throw a football.

 

[Pete_UK]

Instead of the original 3 that you had in your VLOOKUP formula, you
could have refered this directly to the columns of the original table,
i.e.:

VLOOKUP (A1, Sheet1!A:C, COLUMNS(Sheet1!A:C), false)

Then if two new columns are added to Sheet1 between columns A and C
the formula will automatically adjust.

Hope this helps.

Pete

 

[RJB]

Doesn't help this time around, but it sure will next time! Thanks.