Excel fight: VLOOKUP vs XLOOKUP vs INDEX / MATCH
Which formula wins? The result may surprise you.
Referencing information in another Excel tab or workbook is still common practice. Many financial analysts do this almost on a daily basis.
So, it’s worth it that you learn the best formula to do the job.
Round 1: Error transparency
The best formula should tell you if you made a mistake. If you don’t see it right away, it could spell disaster.
INDEX / MATCH does a great job at this. If it can’t find the right value, it shows you an error message. XLOOKUP does this as well. But VLOOKUP completely fails.
See, VLOOKUP is a static measure, while INDEX / MATCH and XLOOKUP are dynamic. That means if something changes in the source table, dynamic measures adapt and either show an error or the correct value you are looking for. But since VLOOKUP is static, it may show you a wrong value without warning. That happens in case you add a column in the source table and you forget to recount how many columns VLOOKUP needs to move to the right to find the value you are looking for.
Winner: INDEX / MATCH and XLOOKUP (tie)
Round 2: Ease of quality control
A great formula should give you a way to quickly check if you are pulling in the right rows and columns. Again, INDEX / MATCH and XLOOKUP do this well. Simply press F2 and you see which values it is looking for.
VLOOKUP, on the other hand, fails this test. Pressing F2 doesn’t show you much. You’ll have to go to the source table to see what it’s doing, which is much more time-consuming and therefore may be skipped.
Winner: INDEX / MATCH and XLOOKUP (tie)
Round 3: Robustness
Once you have built your model, you should be able to update the source table quickly with new data, without having to worry about if your formulas may break. You shouldn’t have to check all your reference formulas in the output tab, just because you made some changes to the inputs. That matters, because input often comes from system downloads, which may get changed without us knowing.
INDEX / MATCH excels in this (pun intended). If you select a full row or column for the lookup array, it will correctly use added columns and find columns that changed their position (eg $2:$2 or $B:$B).
Now, we’ll see a difference between INDEX / MATCH and XLOOKUP. Unfortunately, it’s impossible to assign a full row or column as lookup array. That means, if a row or column is added outside of the lookup array, it’s not picked up by XLOOKUP. Similarily, if a column gets moved to the very beginning of the lookup array, it gets excluded.
You are probably not surprised that VLOOKUP fails in this category as well. Since it’s a static measure, adding a column anywhere means you need to redo the formula.
WINNER: INDEX / MATCH
Conclusion:
INDEX / MATCH is still the best and most robust formula to reference information on another Excel tab or workbook. XLOOKUP comes close (nice try Microsoft), but it still falls short when it comes to adapting to changes to the source table. VLOOKUP should be avoided at all cost, since the risk of missing an error is too high to justify its (slightly) simpler syntax.
If you’d like to learn more about FP&A, I offer help in three ways:
1️⃣ Subscribe to my free newsletter “FP&A Tuesday” here.
2️⃣ Listen to 75 bite-sized mini lessons and get FP&A advice on the go.
3️⃣ Join my live online course FP&A Bootcamp to master FP&A in two weeks.
