Tips in constructing vlookup

We talked about the syntax of vlookup and we had written a vlookup formula successfully in the previous post.  YEAH!

Now we want to ride on the formula by copying it DOWN and to the RIGHT so that we could extend the vlookup for other ranks.  However, all we get is “#N/A”… why????

Simply because we forgot to give Dollar to the formula so that it did not perform in the way we wanted.

Tips for writing vlookup:

lookup_value is usually set to Absolute Column and Relative Row (e.g. $G3) ==> when you copy your formula to the right to get other information, the lookup_value stays at the LEFTMOST column

table_array should always be set to both Absolute Column and Absolute Row (e.g. $A$2:$E$12) ==> logically you always look at the same data source (for expandable datasource, you should use Dynamic NamedRange or Table in 2007 or above; both topics are more advanced and may be discussed in the future)

col_index_num is a bit tricky.  If we input number into the formula, we have to change it one by one when we copy the formula to the right as number won’t change with the position of a cell.

To overcome that, we first put the corresponding column numbers on the top of the table_array (which could be done fairly easy enough and be hidden later).  Then we input the reference B$1 as the col_index_number instead of hardcoding it by 2.  As a result, when the formula is copied to the right, it becomes C$1 (i.e. 3), and D$1 (i.e. 4) etc.

With every argument set with appropriate Absolute and Relative references, you are ready to copy the formula to the Right and Down to get the correct result.  Pls observe the changes in the formula in different cells.

Note: This trick works fine for retrieving data from consecutive columns.  For non-consecutive columns, we may use MATCH which will be discussed in the next post.

A brief explanation of $ in formulation:

Dollar sign $ is for switching a reference from Relative to Absolute, meaning it will NOT change with the position of a cell.

• If you put a $ in front of a Letter (absolute column reference, e.g. $A1), it will always refer to column A even when you move your cell to the RIGHT or LEFT
• If you put a $ in front of a Number (absolute row reference, e.g. A$1), it will always refer to row 1 even when you move your cell UP or DOWN
• If you put $ in front of both Letter (absolute column) and Number (absolute row), e.g. $A$1, it will always refer to the cell A1 regarding the movement of the cell.
• Without $, it is Relative and it moves along with the position of a cell: If you move your cell one column to the right, A1 will change to B1; if you move your cell one row down, A1 will become A2.

As a good habit – always consider the Relative and Absolute reference when writing a formula

Other topics about vlookup:

1. The basic of vlookup
2. Tips in constructing vlookup
3. vlookup with Match 
4. vlookup options – True or False?
5. Advanced vlookup – Text vs. Number
6. Advanced vlookup – Wildcard Characters “?” and “*”
7. Alternative to vlookup – Index and Match
8. Three different ways to do case-sensitive lookup