Tips in constructing vlookup

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

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Formula and tagged . Bookmark the permalink.

5 Responses to Tips in constructing vlookup

  1. Pingback: Alternative to vlookup – Index and Match | wmfexcel

  2. Pingback: vlookup – Text vs. Number | wmfexcel

  3. Pingback: vlookup – True or False?? | wmfexcel

  4. Pingback: vlookup with Match | wmfexcel

  5. Pingback: vlookup – Something you should know about | wmfexcel

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.