Using Vlookup – How to Set the Argument “col_index_num” very Easily

Setting the number of the attribute column is the most annoying part of Using a VLOOKUP formula.

You get it visually on the go or visually before writing the formula?

Anyway, follow these tips to make this process easier…

Tip 1 – Add a row above field headers and write each column number (before writing the formula)

  1. Insert a row above the column headers of the table array (keep one row between)
  2. Write the corresponding number of each column
  3. Write the formula =VLOOKUP(lookup_value,table_array,
  4. Reference the corresponding cell as the col_index_num. For example VLOOKUP(lookup_value,table_array,C2,range_lookup)

Tip 2 – Add a row above field headers and write each column number, name each cell (before writing the formula)

  1. Insert a row above the column headers of the table array (keep one row between)
  2. Write the corresponding number of each column
  3. Name each cell with the name of the field (use named ranges)
  4. Write the formula =VLOOKUP(lookup_value,table_array,
  5. Use F3 or formula Autocomplete to insert the name as the col_index_num. For example: VLOOKUP(lookup_value,table_array,Lastname,range_lookup)

Tip 3 – Count the columns on the go by dragging the mouse

  1. Write the formula =VLOOKUP(lookup_value,table_array,
  2. Select the leftmost column header of the table array and drag the mouse rightward
  3. Read the aid that appears: 2C, 3C, 4C as you drag
  4. Stop at your desired attribute column. Don’t release the mouse button, you have the column: 4C
  5. Delete column references that Excel populated, for example: =VLOOKUP(lookup_value,table_array,H:L
  6. Then specify the col_index_num (without the C). For example: VLOOKUP(lookup_value,table_array,4,range_lookup)

Conclusion

Personally, I find VLOOKUP boring when picking the column number of the table array. I am sure you feel that way too.

Use the above ideas or develop your own method.

That this annoyance doesn’t impede you to use VLOOKUP faster and with comfort…