MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2020: Replace Columns of VLOOKUP with a Single MATCH

August 17, 2020 - by

Excel Replace the Comma Style in Book.xltx. Photo Credit: Javier Reyes at Unsplash.com

The following figure shows a situation in which you have to do 12 VLOOKUP functions for each account number.

The lookup table has columns for Jan, Feb, Mar, and so on to December. You need to do a VLOOKUP to return the 12 results. The initial formula for January is =VLOOKUP($A4,$O$4:$AA$227,2,False). Note the single dollar sign in $A4. This allows you to copy the formula across the sheet and always be looking at the account number in column A. However, the 2 hard-coded in the third argument will have to be edited in after you copy the formula across.

VLOOKUP is powerful, but it takes a lot of time to do calculations. Plus, the formula has to be edited in each cell as you copy across. The third argument has to change from 2 to 3 for February, then 4 for March, and so on.

After you copy the January VLOOKUP to February, you would have to edit the formula to change the third argument from 2 to 3: =VLOOKUP($A4,$O$4:$AA$227,2,False)

One workaround is to add a row with the column numbers. Then, the third argument of VLOOKUP can point to this row, as shown below. At least you can copy the same formula from B4 and paste to C4:M4 before copying the 12 formulas down.

To avoid having to edit the formula 11 times, enter the numbers 2 through 13 in B1:M1. The formula for January becomes =VLOOKUP($A4,$O$4:$AA$227,B$1,False) and can be copied across the sheet.

But here is a much faster approach: add a new column B with Where? as the heading. Column B contains a MATCH function. This function is very similar to VLOOKUP: You are looking for the value in A4 in the column P4:P227. The 0 at the end is like the False at the end of VLOOKUP. It specifies that you want an exact match. Here is the big difference: MATCH returns where the value is found. The answer 208 says that A308 is the 208th cell in the range P4:P227. From a recalc time perspective, MATCH and VLOOKUP are about equal.

With an extra column inserted between Account and January, the formula for the Where? column is =MATCH(A4,$P$4:$P$227,0). This tells you that the account in A4 is found in the 208th row of the lookup table.

I can hear what you are thinking: “What good is it to know where something is located? I’ve never had a manager call up and ask, ‘What row is that receivable in?’”

While humans rarely ask what row something is in, it can be handy for the INDEX function to know that position. The formula in the following figure tells Excel to return the 208th item from Q4:Q227.

You know that you want the 208th item in the January column of the lookup table. Use =INDEX(Q$4:Q$227,$B4). The lack of dollar signs before the row in Q$4:Q$227 allow the formula to be copied to the right and have it point to February and so on.

As you copy this formula across, the array of values moves across the lookup table as shown below. For each row, you are doing one MATCH and 12 INDEX functions. The INDEX function is incredibly fast compared to VLOOKUP. The entire set of formulas will calculate 85% faster than 12 columns of VLOOKUP.

The single formula can be copied to all months and all rows. This screenshot is showing September, where the formula is =INDEX(Y$4:Y$227,$B4).

Note

In late 2018, Office 365 introduced new logic for VLOOKUP that makes the calculation speed as fast as the INDEX/MATCH shown here.

Title Photo: Javier Reyes at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.