xlookup v index

henryg

Board Regular
Typically I want to extract the latest balance (the last figure) from a table of summed data which is sorted in ascending date order. Up to now, I have used the Index function, as in

INDEX(Table16[Column5],COUNTA(Table16[Column5])

but I always find Index unintuitive, so thought of the new Xlookup and easily and quickly came up with the formula

XLOOKUP(TODAY(),Table16[Column1]],Table16[Column5]],-1,-1))

The Index formula is more concise, and I wonder about calculation times. I find the Xlookup formula more intuitive to construct, but am I silly to use it in place of the more concise Index?
 
Last edited:

jasonb75

Well-known Member
Without seeing the underlying vba code used to run your XLOOKUP udf we are not in a position to judge performance, however I would suspect that the native index function will offer superior performance.

If your task is as simple as your post, you could just use =LOOKUP(1e+100,Table16[Column5])
 

jasonb75

Well-known Member
Thanks, Peter!

I happen to be one of the mere mortals not privy to such things. Looks like it has the potential to be quite a useful function, think it might be a bit more than is needed for the OP's purpose here though.
 

henryg

Board Regular
Yes, indeed, I have the Insider version, and XLOOKUP is a bit of a new toy! Mike Girvin at ExcelisFun has a comprehensive video at

https://www.youtube.com/watch?v=WO6Kjba5EPc

But it is really good, and resolves a lot of problems with VLOOKUP, and obviates the need for INDEX/MATCH to solve them in many/most situations. For some reason I have always had a issues using INDEX/MATCH, whereas I find XLOOKUP easy and intuitive. I'm hoping it is not a lot slower, but have no means to test that.
 

Some videos you may like

This Week's Hot Topics

Top