Excel: VLOOKUP for Speed

 

COMPETITIVE EXCEL

  First, a bit of history. Competitive Excel events trace their roots to a competition named ModelOff that debuted back in 2012. A few thousand “Excellers” competed, with the top 16 winning a trip to New York City to compete head-to-head. Today, the Financial Modeling World Cup has taken up where ModelOff left off. Thanks to Zoom, the finalists no longer need to fly to the same city. Everyone competes from their own office, streaming their Excel screens so viewers can watch at home.   In a typical round, competitors are given 30 minutes to solve an Excel problem with five levels of complexity. Solving all five levels gets them 1,000 points. I’ve been a judge at the live events and one of the on-air commentators for the Zoom events, including the all-star event on ESPN2. Anyone who makes it to the finals is incredibly fast in Excel. They know keyboard shortcuts for just about everything and rarely need to touch their mouse. Most competitors will pry the F1 key off of their keyboard so they never accidentally press the Help key when they are reaching for F2 (to edit a cell) or Esc (to exit the Edit mode). Why? Because pressing F1 costs 8 to 10 seconds while Excel Help loads, and no one wants to risk losing 10 seconds when $25,000 in prize money is on the line.   Hailing from New York City, Diarmuid Early was a finalist in 2013 and won ModelOff in 2014. He won the Financial Modeling World Cup in 2021 and is considered by some to be the fastest Exceller in the world. During a recent event, he solved the entire 30-minute problem in under 14 minutes. A few days after an event, he will often discuss his solution on his YouTube channel, first solving the problem at high speed and then slowly walking through his techniques, explaining them step-by-step.   Frequently, people competing in an Excel event will try to solve the simplest level first so they get points on the board. In contrast, Early will try to build a solution that solves the hardest level. These are worth more points. Plus, the easier levels are usually simpler versions of the difficult level. By writing the difficult logic first, Early has essentially solved the easier levels, although he might need to simplify the formulas.  

SHORT RANGE NAMES

  In a recent puzzle, competitors needed to write a lookup formula that would convert a symbol to a number of points. You might solve this using VLOOKUP, XLOOKUP, or INDEX and MATCH. But before writing that formula, Early named the B17:C24 lookup table with the one-character name G.    

Using keyboard shortcuts, he pressed Alt+F3 to shift focus to the Name box located to the left of the Formula Bar. Since this table started with a gem, he typed the “G” and pressed Enter (see Figure 1). From this point forward in the spreadsheet, he could quickly refer to the lookup table in his formulas by simply using the name of G.

 

Later, Early needed to perform a lookup to convert a symbol to a score. He used the remarkably short VLOOKUP formula of =VLOOKUP(S6,G,2,0), as shown in Figure 2. Thanks to the G-named range, he didn’t have to type $B$17:$C$24 in the formula. The final argument of 0 is equivalent to False.

    Many people would point out that XLOOKUP or INDEX/MATCH offer more flexibility than VLOOKUP, but Early has a different opinion: “While the people who use XLOOKUP and INDEX/MATCH tend to talk about the potential problems with VLOOKUP in a speed competition, the VLOOKUP will always be faster to enter because you only need to refer to one range. With XLOOKUP, you would have had to define two named ranges so you could point to both the lookup vector and the results vector.”   For example, you could have defined J as a named range for B17:B24 and K as a named range for C17:C24. Creating two named ranges takes double the time. Even though the resulting XLOOKUP is slightly shorter, the time lost naming J and K makes the VLOOKUP preferable in Early’s eyes:  

• =VLOOKUP(S6,G,2,0)
• =XLOOKUP(S6,J,K)
• =INDEX(K,MATCH(S6,J,0))

 

An Excel competition might be one of the more odd events televised on a sports network, but it shows that esports aren’t limited to just video games. If you’re the Excel guru in your office, consider entering the next Financial Modeling World Cup competition yourself.