As a data Analyst, you will be using Excel A LOT. Some examples of its uses could be to validate data within a dashboard, create pivot tables for simple analysis, or even curate dashboards (personally not my favorite). As you use Excel, there will be many things you would want/need to do, and one of those would be looking for a specific cell from one table to another that is matched with a key. This is where LOOKUP formulas such as INDEXMATCH, VLOOKUP and XLOOKUP are effective.
Today, we are going to talk about XLOOKUP, and why you should learn this formula well to become an effective data analyst in the working world. If you are unsure on what XLOOKUP is about, I’ve created a post on what it is, and how you can effectively use it in Excel.
Easy Data Mapping
If you were to not use any lookup formulas (i.e. VLOOKUP, XLOOKUP, INDEXMATCH), then doing data mapping will become a manual and tedious task that can be prone to human errors. The use of these lookup formulas makes it extremely easy to do any data mapping activity. All you will have to do is identify the keys to map the various tables together, and you’re good to go.
Customizable output if XLOOKUP not found
Even though INDEXMATCH and VLOOKUP allow you to LOOKUP data according to what you specify, it does not allow you to provide an output if there is no LOOKUP value was found. Instead, it will show as #N/A, which makes it look very unpresentable.
XLOOKUP on the other hand, allows you to customize what output should be shown IF there is no LOOKUP value found. This means you can include things like integers (1,3,4, etc.), and texts (“Empty”, “Blank”, etc.) or even just leave it as a blank. This allows the data presented to be much more presentable and clear.
Applying LOOKUP logic onto columns on the left
The one BIG problem that I have with VLOOKUP is that it can only be done with columns on the LEFT of the look-up value. This makes looking up certain values troublesome as you will have to re-arrange the columns before implementing the VLOOKUP formula.
As for XLOOKUP, you have the flexibility to do your LOOKUP formula regardless of how the LOOKUP table is organized. This makes it more convenient to do any lookup functions as compared to VLOOKUP.
Personal Thoughts
VLOOKUP was one of the first few formulas that I’ve learned in Excel, but ever since I discovered XLOOKUP, I’ve never used it since. Personally, XLOOKUP feels like the “improved” version of VLOOKUP, giving you the capabilities to customize output for values that could not be looked up, and also the added flexibility to perform your lookup functions more effectively. XLOOKUP is something that I use a lot in my job, and I am glad that it is available for me to perform my day-to-day tasks more effectively.
What do you guys think about XLOOKUP? would you guys be using/are already using it? Let me know by commenting down below.