If you need multiple columns, Index-Xmatch is much more efficient because you can place the Xmatch into a helper column and reference it multiple times.
Even beyond that, I'm generally not going to write a Python script or use PQ/PBI unless the situation actually warrants it.
A common example for what I mentioned above would be data validation, such as comparing two tables which should contain identical data. There's no point in adding yet another thing that will need validating.
Back when I was an accountant, I had a lot of JEs that would use formulas like that. There's no other system except manually inputting the entry into the ERP.
Returning data from multiple columns is just a pretty basic use case.
You can't reference a variable in a LET function outside of the formula. Unless you're saying to store it with the name manager which is basically the same thing as using a helper cell but slightly slower.
Depends on the size and complexity of the dataset you need to do the lookup. Xlookup is syntactically and operationally very easy to use, but it incurrs quite the computational overhead. I think in testing it requires something like double the computation time as index/match. For a couple thousand lines, the time difference is negligible. But once you get into multiple tens of thousands of lines, with multiple lookups, or worse, multi-variable boolean lookups, index/match becomes much more attractive just to keep your workbook from chugging after every click.
If I'm doing something that big and need to stay in Excel, that's when I use power query and just tell people to "hit the refresh button, no, not the Essbase refresh button, the other one."
I think there is still a bit of an area where index/match is noticeably faster than xlookup, but still not big or complex enough to jump into power query.
Works to do things like pick the nth item in a list. Use filter() to get an array of all of the items that match your criteria, and then use index to select the nth item.
I'm pretty sure I've seen very intricate and elaborate videos demonstrating how xlookup is actually the least performant option between it, vlookup and index/match - not that you would ever notice the difference unless you were constantly dealing with Excels with 10m rows though, for any normal person xlookup should be the go to in 11 out of 10 situations.
123
u/V1per41 21h ago
Index match was great before xlookup became a thing. Only thing I really use index for now is index filter