Does Vlookup have to be first column?

Answer 1

The vlookup formula can go anywhere in your workbook, but the table that contains the value you want to return must start with the column that contains the matching field.

A closer look at the vlookup function reveals why. The vlookup function has four inputs:

1.) The cell in table A that also exists in the the leftmost column of table B.

2.) Table B (or a subset thereof).

3.) The column in table B that has the value you want to return.

4.) A specification of whether the match must be exact.

The vlookup function does not provide an option to specify which column in table B should have the matching value from table A. The function assumes/requires that the match will be in the leftmost column of table B.

Sometimes, this is problematic, such as when you need to do multiple lookups on different fields. Afterall, they can't all be first. In such cases, INDEX and MATCH can be a more viable solution.

If you're trying to join two tables, also consider the data model option for pivot tables via PowerPivot, which can eliminate the need for vlookups.

VLOOKUP Tip:

Be sure to use dollar signs to fix the references to table B if you are going to copy the vlookup formula to other cells. Without the fixed references, the formula will return correct answers in some cases, and miss other matches. It is a very common oversight and can lead to confusing results. (Fixed references might also be needed for the first argument.) To enter dollar signs quickly, highlight the cell references (i.e., A1:G10) and press F4 to toggle through the various combinations.

I hope that helps with the question at hand and helps you build your skills in Excel.