XLookup Wins!

Yes, yes, I know there's a whole post about how much I love VLookup. BUT! I'm ready to admit that it's been replaced. Shocking, I agree, but I can admit when a better formula comes around! You don't have to count columns, or worry about the order of your columns, and you can have a custom error message right in the same formula. I'll keep this short and just show some examples of how you can use XLookup in place of VLookup.

Shortest versions of each:

=Vlookup(D2,J:L,3)

=Xlookup(D2,J:J,L:L)

If you want the message "Not Found" to display instead of #N/A for errors:

=IFERROR(VLOOKUP(D2,J:L,3,0),"Not Found")

=XLOOKUP(D2,J:J,L:L,"Not Found")

So, just like my VLookup post with a full breakdown, here's the breakdown of XLookup:

Here are more examples of that same formula if your data is in a separate worksheet:

=xlookup(d2, Sheet1!J:J,Sheet1!L:L,"Not Found")

or a separate workbook:

=XLOOKUP(D2, '[GENERICWORKBOOK.xlsx]Sheet1'!$J:$J,'[GENERICWORKBOOK.xlsx]Sheet1'!$L:$L,"Not Found")

Happy Spreadsheeting!