In this Excel Tutorial I am going to discuss Excel 2013 Vlookup Function. In this Tutorial I will focus on Table Array Keeps Changing.
I have a spreadsheet from previous Excel Vlookup Tutorial where I have a table with value with Vlookup which return Blanket.
I will copy 90001 value and paste below. I will go down with Blanket value. You can see that we get not applicable ( #N/A).
The reason why we get Not applicable is because when I move the formula one down, everything move down and in the table it's one row lower but when I go to the original one ( Blanket ) I had everything selected. Pictures below, show you this steps.
The reason why it cannot find it it's because it is nor part of table array I have got.
To solve that I common approach to put absolute references. You don't have too put all of them because we will scroll them down.
You can have a table - a list of value at the right side and what you should do when you add an extra item at the end. When you absolute references it will not work and your item will not appear. You can replace table array and select entire column.
When we references entire column and move it down and up still references the entire column but we cannot put any values related to this data because it may return incorrect values.
In the next example I will select the table and select Insert as table. You can see the Create Table window where I click Ok because I don't have headers.
It is called table 3 - you should see this at your left side Now, I will references the table ( Table3(#All)). I deleted the previous column values and selected all table.
When I scroll down Blanket value I will not have a problem with #N/A . When your items are added to this table it will automatically expand it will still be the part of the same table. You have to remember that the first column need to be the value we want to find, it cannot be in the middle of the table it has to be at the beginning but if it's in the middle you can always use a formula to reference the middle that will mean it is a beginning but that's mean it's limited only to one Vlookup per table and you cannot have multiple for the same table.
I hope this Excel Tutorial was helpful.
Katie & Emil.