If you have ever crossed Excel paths with me, you know that I am a HUGE proponent of XLOOKUP. So if you’re unfamiliar with the XLOOKUP, get ready to get your socks knocked off and send VLOOKUP to the retirement home where it belongs.
What is XLOOKUP?
XLOOKUP is Excel’s new function introduced to replace the legendary VLOOKUP and HLOOKUP. This dynamic tool offers more flexibility and eradicates many limitations posed by its predecessors. It provides a simpler, more powerful way to perform vertical and horizontal lookups, a common necessity for analysts and managers alike.
How to Use XLOOKUP
Here’s a step-by-step guide to help you master XLOOKUP:
Step 1: Initiate the function with ‘=XLOOKUP(‘
Step 2: Enter the value you’re looking for. This is what XLOOKUP will search for in your table.
Step 3: Specify the range where Excel should look for this value. This is your “lookup array”.
Step 4: Specify the return array, i.e., the range from which Excel should return a value when it finds a match.
Step 5: [Optional] Specify a “Not found” text. If Excel doesn’t find the lookup value, it will return this text.
Step 6: [Optional] Determine the “Match mode” (exact match or next smallest item) and “Search mode” (search from first to last or last to first).
Step 7: Close the function with a ‘)’, and hit enter!
It looks like this when you put it all together:
‘=XLOOKUP(lookup_value, lookup_array, return_array, [Not_found], [Match_mode], [Search_mode])’
Why XLOOKUP Beats VLOOKUP
- Simplicity: Unlike VLOOKUP, which needs a column index to return a value, XLOOKUP only requires a return array. No more counting columns!
- Versatility: XLOOKUP can perform both vertical and horizontal lookups. No need for HLOOKUP anymore.
- Flexibility: XLOOKUP can look both forwards and backwards. This is a significant improvement over VLOOKUP, which only scans to the right.
- Error Handling: With XLOOKUP, you can specify what Excel should return if it doesn’t find a match, reducing error outputs.
In conclusion, while VLOOKUP has served us well for many years, XLOOKUP comes out ahead with its versatility and simplicity. As we continue to explore more tools to streamline our work, remember: stay curious, stay innovative.
Stay tuned for more tips and insights from Arsenal Insight and check out the video below on XLOOKUP!