When creating a VLOOKUP, it is quite common to specify the lookup range by moving to the desired sheet, selecting the required cells, and then moving too the next part of the VLOOKUP. Using this method, the required cells may be selected by looking at the column headings, data held within, or selecting the whole region bounded by blank columns – this does not remind you of the number of columns that exist in that range.
When a large number of columns have been selected (and especially is the range runs off the screen), it is quite easy to miscount, and specify a column number which is outside the number of columns in that range as the column to be returned by the VLOOKUP.
Unfortunately, Excel tells you this is an error, by returning #REF. This error message doesn’t tell you which part of the formula entry is in error – if the cell you were looking up showed #REF, then this would cause the problem.
The VLOOKUP Assistant checks to see if you’re attempting to return a column outside this range, and changes the VLOOKUP to return the last column in the range. It will show you what value this would return if the VLOOKUP then worked. This might not be the column you want, but by changing the VLOOKUP in this way, it does help you to identify that part of the formula which was in error.