XLOOKUP is the future
You might still be a VLOOKUP fan and that’s perfectly fine, but XLOOKUP is the function that can transform your spreadsheets, making them far more efficient and reliable.
I started my excel journey back in 2017 when I was just beginning my career as a junior auditor at one of the Big 4s. I was learning a lot about auditing, accounting, people, and most importantly about excel.
VLOOKUP was something that was used at least 50 times per day, it became so used that I even had dreams about it. Jokes aside, VLOOKUP is a lookup and reference function that is used by all financial professionals, from juniors to seniors to manager and maybe even partners.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])The function works pretty straight forward, you input the lookup value, then you select the table array from where you want to get the results and finally you specify from which column you want the results to be displayed.
All works fine until you realize that this search only works from left to right, then you must always count the columns manually and completely breaks if columns are inserted or deleted.
So the question is quite simple, is there a way to fix these issues? The answer will always be yes because if you are an excel wizard, you will know that there are many solutions for one problem.
However, back in march 2020, Microsoft updated Excel with a brand new function that was set to revolutionize the world of boring accountants: the XLOOKUP!
Just like VLOOKUP, XLOOKUP is a lookup and reference function but BETTER because:
there is no column counting involved
can look left OR right (hell yes)
has built-in exact match by default
it is cleaner and easier to read
handles errors nicely (praise the lord)
can return arrays (dynamic results)
and is more robust for automation
Mainly, these are the 7 reasons why this formula should replace and make you forget about VLOOKUP. It is definitely better, more versatile and can make your work faster and more efficient.
=XLOOKUP(lookup_value, lookup_array, return_array)Have you heard about XLOOKUP? Do you think it is genuinely better than VLOOKUP? If yes, please do leave your thoughts in a comment below.