The image below shows a VLOOKUP entered incorrectly.The wrong cell ranges are being referenced for the lookup_value and table array.Click the Design tab under Table Tools and change the table name in the box provided.The VLOOKUP below shows a table named Fruit List being used.A limitation of the VLOOKUP function is that it cannot look to its left.It will look down the leftmost column of a table and return information from the right.Typically it may be just the table_array that needs locking.
This argument is optional, but if left empty, the TRUE value is used.
Click on the references within the formula and press the .
In this example both the lookup_value and table_array references were made absolute.
Because this is entered as an index number, it is not very durable.
If a new column is inserted into the table, it could stop your VLOOKUP from working. The quantity was in column 3, but after a new column was inserted it became column 4. One solution might be to protect the worksheet so that users cannot insert columns.