Start Vlookup not updating automatically

Vlookup not updating automatically

Please download it and pin it up at work, you can even forward it onto your friends and co-workers.

For example, in the screen shot below, the order form is on the Orders worksheet, and a VLOOKUP formula in column D pulls the cost from a pricing table on the Prices worksheet.

But, what happens when a column is inserted between Column A and Column B?

The Range A2: B10 will automatically update to become A2: C10, but the lookup column number will remain the same.

Just imagine for a moment that we have entered the following formula into Cell E6 (see screenshot below).

This formula is returning the value from the 2nd column (Column B) – result 21.

PS The above code is for the people looking for a macro to solve the issue.

Reference: VBA Excel function. This can be time consuming if other Excel files are loaded because all Excel sheets of all opened workbooks will be calculated again!

If the price list lookup table workbook is closed, the VLOOKUP formula will still work, and the full file path for the price list lookup table workbook will be shown in the formula.