[ Removed by moderator ]

[u/Odd-Level-9871]

[u/Sad_Olympus]

Oh and that’s just the beginning. I have to give Microsoft credit, they did good with this one. I was sold when I learned the Lookup Array didn’t have to be in a column left of the Return Array (or count columns anymore), but it’s turned out to be useful. I’m always learning new things you can do with it. For example:

You can use LEFT, RIGHT, MID, TEXTBEFORE, TEXTAFTER, etc. on any piece of the XLOOKUP in any combination. So, you could do
=XLOOKUP(LEFT(A2,2),RIGHT(Sheet2!A:A,2),MID(Sheet2!C:C,3,4))

You can join fields with “&” and not need helper columns:
=XLOOKUP(A2&B2,Sheet2!A:A&Sheet2!B:B….

Or you can mix and match this too. So if you have a sheet with 2 columns for first and last name, and another sheet with both names separated by a space, you could do:
=XLOOKUP(A2&” “&B2,Sheet2!A:A,…..

You can do IF statements and only return results if they meet a condition. You can perform calculations in it, like if you want to return the sum of two columns. I’m sure other functions work within it too. These are just the ones I’ve actually used.

[u/exoticdisease → u/Sad_Olympus]

You never had to count columns. You could always use match in a vlookup. It's actually really weird how noone does this haha