The fix I seem to do the most on this question has to do with the way the formula was built! When you build a vlookup, you are giving it several parts to work with so that it can evaluate and return a result. The problem typically occurs due to inappropriate referencing in the formula itself.
A VLOOKUP has these required parts and one optional argument:
Lookup = the cell reference is it using as the value to match in the table array.
Table Array = the table where it goes to find the match
Column Index Number = cell reference that contains the value you want displayed.
The Optional Argument = True for an approximate match, or False for an exact match.
=VLOOKUP(‘Tshirt Orders’!C3,ColorList!A1:B6,2,FALSE)
The Vlookup works fine until it hits a little snag…and you begin to see “#N/A” in the cells. This error occurs because the table array is not absolutely referenced. When it is then copied or auto filled, it updates the reference to the table array to row that it does not exist on!
How to correct this? You apply an absolute reference to the table array by clicking in the references and pressing F4 (function key- F4). This updates your formula to read like this:
=VLOOKUP(‘Tshirt Orders’!C3,ColorList!$A$1:$B$6,2,FALSE)
(tip: Click after the cell reference, then press F4)
By applying absolute referencing, you are telling Excel that when it copies the formula to update the cell references every where, except the table where it is searching!
Help Topics: Absolute References, VLOOKUP
