vlookup and index+match

For this exampple of vlookup and an alernative, we are going to use the following names for the region and regional manager to add into our sales data.

Region	Regional Manager
North	Robert Mitchell
South	Sarah Thompson
East	Kevin Brooks
West	Jennifer Hayes

The main difference betwen vlookup and index+match are the syntax and performance on larger datasets. Vlookup will be slower when you are working with very large datasets with lots of other formulas. Vlookup can only look up values to the right of the lookup column, while INDEX+MATCH can look in either direction

Creating vlookup

Create a new sheet, and add the names into the sheet

Then press control+t to create a new table (be sure to check the add headers box).

Next go back to the sales_data sheet, and then create a new column. We will enter the formula, =vlookup([@Region],Table2[#All],2,false)

Manually Create

In a new cell in the sales_data, type =vlookup( then click your region in the same row. At this point, your formulad will say =vlookup([@Region]

After clicking the region cell, press the comma - , then navigate to the second table you created with the regional managers, highlighting all of the values

Press the comma - , again and type 2,false) and press enter.

The 2 is the number of columns from the index (always 2+n beyond the index to match from, so column D would be 4). The false is very key, it is partial match or exact match, use false most of the of the time.

And the result will be:

Troubleshooting

There will be times where you have incomplete data or a match isn't the same, you will see values of #N/A in the sheet, like below. The problem with #N/A values is, you will not be able to do any math as Excel will see the #N/A as a string (like "abc") and not a number (like 123), and you cannot add b to 2.

There are a few ways to fix this:

1. Add a IFNA or IFERROR to the front of the VLOOKUP statement, along with the value you want to be replaced, such as FIX ME or TBD or 0 if numbers.

2. =IFNA(VLOOKUP([@Region],Table2[#All],2,false),"TBD")

3. =IFERROR(VLOOKUP([@Region],Table2[#All],2,false),"FIX ME")

1. Add the data to the table that you are VLOOKing up to.

2. Make sure the data is the same in both places (e.g. Regional Manager exists for West, in this case)

Index+Match

TBD

XLOOKUP

TBD