PROBLEM 1: SPELLING (EXTRA SPACES?)

A simple problem accounts for most problems with VLOOKUP. Spelling generally has to be 100% accurate. Make sure the entries in the database are accurate, as well as the value you are looking up. Also, do you have extra spaces after your entries? This classic mistake is difficult to spot and can be infuriating. Precise spelling clears up a lot VLOOKUP formula problems.

PROBLEM 2: LACK OF ABSOLUTE REFERENCES IN TABLE REFERENCE

It is natural to want to ‘drag down’ a VLOOKUP formula to apply it to multiple cells. To do so can create problems if the correct referencing technique is not used. When copying the formula to other cells, we wish to ‘fix’ the table reference, so that all cells refer to the same table. This necessitates use of absolute references or ‘the dollar signs’. See how Chris integrates them in the video.

PROBLEM 3: NOT REFERENCING THE ‘FURTHEST LEFT’ COLUMN

VLOOKUP requires a table reference and does only one thing with it: it tries to find the look up value in the leftmost column of the table. It cannot find a lookup value in the ‘second-from-left’ or any other column! Make sure the leftmost column of table contains the lookup values – you may have to adjust the table reference, or the layout of the data.

PROBLEM 4: LOOKUP COLUMN OUTSIDE OF TABLE RANGE

You might wish to adjust the VLOOKUP formula to return data from another column, or add an additional VLOOKUP formula to do the same thing. It is important to remember that VLOOKUP can only find columns within the table range, however. An #REF! error may mean the proposed column index number exceeds the number of columns in the table. To resolve this, review and enlarge the table reference to encompass more columns.

PROBLEM 5: LACK OF UNDERSTANDING OF ‘APPROXIMATE MATCH’

The problem that is most difficult to explain! People often avoid dealing with the last component of the VLOOKUP formula that determines the match type, which we can set to exact or approximate, by excluding it altogether. It is best to include it, to make things clear to Excel and to other spreadsheet users, and for your own learning benefit! Before we go further, it’s worth noting that, in most cases, exact match (FALSE or ‘0’) is most appropriate; this is because people are usually trying to match ‘exact’ text values (see the example in the video). If in doubt, set this value to false (‘0’).

So, what is approximate match for? VLOOKUP with approximate match helps is slot a number into a range of numbers. The values we input in the leftmost column of the table act as ‘lower bounds’; Excel constructs ranges and slots the look up value into the appropriate one. This can be difficult to envisage: check out the example in the video, and download the example file.

So, did you manage to fix your Excel VLOOKUP problem? Thanks for reading and leave a comment on the video.