Stop Using VLOOKUP

One of the most powerful things you can do with a spreadsheet is to use it as a lightweight database. The core function of a database is to allow one to look up key/value pairs. But the traditional way of doing this using vlookup had some major flaws. There’s now a better way.

The Bad Way

This is the way that the vlookup function nudges you to use it, and it’s bad.

Let’s start with how vlookup works:

The first input is the cell to look up in the database table; in this case “Jimbo”. Second is the full table. Third is the number of the column within the table from which to return a value, and fourth should almost always be set to ‘false’ meaning that the table is not sorted.

This all looks great. The lookup value is returning Age as I want. Now let’s add a new column to the table.

Now the lookup value has completely changed because Eye Color is now the second column in the table. This kind of mixup is dangerous in a complex, difficult to debug spreadsheet, and makes it very un-robust.

The Good Way

Use XLOOKUP instead. This function uses two separate inputs for key column and value column, so does not suffer from the vlookup problem.