MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Replace 12 VLOOKUP with 1 MATCH

September 15, 2017 - by Bill Jelen

Replace 12 VLOOKUP with 1 MATCH

This is another formula speed example. Say that you have to do 12 columns of VLOOKUP. You can make it faster by using one MATCH and 12 INDEX functions.

In the following figure, you are going to have to do 12 VLOOKUP functions for each account number. VLOOKUP is powerful, but it takes a lot of time to do calculations.

Sample Data Set with VLOOKUP Formula
Sample Data Set with VLOOKUP Formula

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.

3rd Argument Changes by Month
3rd Argument Changes by Month

One workaround is to add a row with the column numbers. Then, the 3rd argument of VLOOKUP can point to this row. At least you can copy the same formula from B4 and paste to C4:M4 before copying the whole set down.

Using Helper Row Numbers
Using Helper Row Numbers

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 of 208 says that A308 is the 208th cell in the range P4:P227. From a recalc time perspective, MATCH and VLOOKUP are about equal.

Helper Column with MATCH Formula
Helper Column with MATCH Formula

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, the INDEX function can use that position. The following formula tells Excel to return the 208th item from Q4:Q227.

INDEX Function to Return Item from the List
INDEX Function to Return Item from the List

As you copy this formula across, the array of values moves across the lookup table. 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 Result Data Set
The Result Data Set

Watch Video

  • Say that you have to do 12 columns of VLOOKUP
  • Carefully use a single dollar sign before the column of the lookup value
  • Carefully use four dollar signs for the lookup table
  • You are still hard-coding the third column argument.
  • One common solution is to add a row of helper cells with the column number.
  • Another less-efficient solution is to use COLUMN(B2) inside the VLOOKUP formula.
  • But, doing 12 VLOOKUP for each row is very inefficient
  • Instead, add a helper column with a heading of WHERE and do a single Match.
  • The MATCH takes as long as the VLOOKUP for January.
  • You can then use 12 INDEX functions. These are incredibly fast compared to VLOOKUP.
  • The INDEX will point to a single column of answers with $ before the rows.
  • The INDEX will point to the helper column with a $ before the column.

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2028 replacing many vlookups
  • with one match click that I on the top
  • right hand corner to get to the playlist
  • I'll be podcasting this entire book hey
  • welcome back to the MrExcel net cast
  • I'm Bill Jelenen well it's a classic
  • problem we have to do vlookup once for
  • every month right and you can be
  • incredibly careful here about pressing F
  • for one to three times to lock that down
  • to the column and then pressing f4 once
  • the lock down the whole row but when you
  • get to this point the comma 2 comma
  • false that too is hard-coded and as you
  • copy that across you're going to have to
  • edit the two to a three all right now
  • one inefficient way to do this a way
  • that I don't like is to use the column
  • of B one column B one is of course 2 but
  • as you copy that across see that it'll
  • change to the call of C 1 which is 3 but
  • think about this this is constantly
  • figuring out the column number over and
  • over again so what I see people do and
  • why you know prefer more than the
  • columns is we'll control drag that put
  • the numbers 2 through 13 up there and a
  • helper cell and then when we get to this
  • point we go up and specify that column
  • number press f4 1 2 times the locket
  • time of the row comma false and so on
  • but even with that method vlookup is
  • incredibly inefficient because it has to
  • go search through all of these items
  • here until it finds a 308 and that's the
  • figure out before when it then moves
  • over to seed for it forgets that it just
  • went and looked and it starts all over
  • again
  • alright so you have one of the slowest
  • functions in all of Excel the vlookup
  • comma falls being done over and over and
  • over for the same item so here's the
  • much much faster way to go we're gonna
  • insert a helper column and this helper
  • call them I call it where as in where
  • the heck is a 308
  • we'll use a match equal match look for a
  • 308 in the first row of the table press
  • f4 there comma 0 for an exact match all
  • right it tells us that hey look at that
  • it's in row 6 how awesome is that
  • but as we copy down see it's in
  • different places all the time all right
  • now this match takes as long as the
  • January vlookup takes there they're dead
  • even but here's the amazing thing from
  • there we never have to do a vlookup for
  • the rest of the row we could just do
  • equal index index says here's an array
  • of answers I'm gonna go to the January
  • sales and I'm very carefully here press
  • f4 one two times so I lock it down to
  • four and 227 but the queue is allowed to
  • change as I move comma and then it went
  • to what want to know what row well
  • that's gonna be the answer in b4 I'll
  • press f4 one two three times to get the
  • dollar sign before the B all right copy
  • that across this formula these index
  • formulas will happen these twelve will
  • happen in less than the time it would
  • take to do the February vlookup all
  • right if we put Charles Williams timer
  • on this this whole thing will calculate
  • an about 14% of the time of twelve
  • vlookups your manager doesn't want to
  • see the we're fine just hide that column
  • everything keeps working all right this
  • is a beautiful way to speed up the
  • twelve months or the fifty-two weeks of
  • vlookups all right this tip and so many
  • more tips are in this book click the I
  • on the top right hand corner there you
  • can buy the book ten bucks ebook
  • twenty-five bucks for the print book all
  • right so today we had a problem where
  • twelve columns of vlookup you can
  • carefully put the dollar signs in the
  • dollar signs in but that hot third
  • argument still has to be hard coded you
  • could use column abhi - I'm not a fan of
  • that because there's hundreds of rows
  • times twelve columns where's calculating
  • that over and over just use a helper
  • cell in a row put the numbers 2 through
  • 12 and point to that it's still
  • inefficient though because vlookup after
  • it figures out January it has to start
  • back in the beginning for February so I
  • recommend adding a column with a heading
  • of where and doing a single match there
  • that match takes as long as the vlookup
  • for January but then the 12 index
  • functions will take less time than the
  • vlookup for February and you've trimmed
  • a whole bunch of time again careful with
  • the dollar sign
  • in the index function in both places one
  • just before the rose and the other one
  • before the columns a mixed reference in
  • both of them way I want to thank you for
  • stopping by we'll see you next time for
  • another net cast from MrExcel

Download File

Download the sample file here: Podcast2028.xlsx

Title Photo: roegger / Pixabay