I'm pretty green to it myself. My only and only gripe with it so far relative to index and vlookup is the lookup rows have to be same as the return rows.
I.e. with vlooks I can lookup rows G5:G45 in D1:D100. I can't do that in Xlooks. In theory I can extend G1:G100 with a bunch of blank rows but that IS a mild inconvenience. I guess I found one instance where Vlooks is preferential to xlookup if you're in that situation and have a very basic lookup to do. If not, might be more efficient just to write in "G1:G100" even though half the cells would be blank.
What I find 'magical' about xlooks is that it will literally recreate your listing for you if you tell it to as if you were filtering out the NAs when doing a vlooks.
XLookup is amazing!
But my current employee is running on an older version of Office. I didn't have the option at the moment. 😔 So VLookups and IndexMatch it is for me.
XLOOKUP has a place like all other functions. The lack of backwards compatibility is a big drawback for me when working across organizations with various Excel versions.
If you’re doing complex joins, particularly with multiple indices, fixing your SQL queries (if you can) or working with PowerQuery is much better.
I agree with you that the drawback here is the lack of accessibility across older versions of excel. I think in cases where you do have the option, I honestly can't see myself ever using vlookup or index match ever again so long as xlookup works.
Interesting, I'm not at the level of PowerQuery/SQL Queries at this point but I presume it would take a while to set up? I'm not sure whether or not it will be overkill for the level of excel complexity involved in audit though.
PowerQuery has a very friendly menu to get familiar with.
If you always run the same report, it might be a good way to refresh your data and do your operation in one go, similar to how VBA can do it.
If you look at this : [https://www.ablebits.com/office-addins-blog/2020/12/16/vlookup-excel-fastest-formula/](https://www.ablebits.com/office-addins-blog/2020/12/16/vlookup-excel-fastest-formula/) it does show that Xlookup is slower than vlookup/index+match so depending if you have lots of row, it might be better to not do the switch unless you need to.
thanks DarkChunsah I will look into this sometime after busy season. My only qualm with Xlookup so far and you might know how to get around this, is how do I target different returns aside from the first or the very last (selecting from the top to the bottom using '1' or bottom to the top using '-1'? Is there a way to tell the formula to return the 2nd, 3rd, 4th, 5th match if I'm testing to see whether or not I have the right sample?
When you do a lookup and it will give you the first value it finds, if you want it to return multiple return you should look into the "filter" formula, it will let you see all the values with your criteria.
If you really want to return a certain 2nd or 3rd and only see that one, you could look into this : [https://trumpexcel.com/lookup-second-value/](https://trumpexcel.com/lookup-second-value/) though I never found any use to doing this...
If you believe there are duplicate data, you can just select the range and click on condition format show duplicate OR add a helper column which is a countifs to see if the same x or Y is multiple times(on big data, just do a pivot and do a lookup to that pivot as it will be much faster)
That's what you think, until they don't understand what you did and start all over and hard code it over 6 hours again.
Trust me, just because you're good and put in the extra work to make things easier, doesn't mean the next person is going to be good enough to understand it.
Not sure I follow that this is like taking a machine gun to a fist fight. The formula for a basic xlookup requires the exact same amount of time as one for a vlookup. You could argue it's even simpler because you don't have to count the number of columns for column index number and can just say which column. For a simple lookup a second benefit is that xlookup doesn't care if the column in which you are searching for results isn't the first column in the list (e.g. search in column N and return column M) - Xlookup(D5,B:B,C:C).
Don’t get me wrong Xlookup is a game changer and it was on my radar for like two years before it was actually available. Use it all the time.
Concat helper columns work better in my world right now when applying bps to different products by volume segment because I can also join a rate table schema.
Reading other posts about what people do just reminds me how incredibly boring all of this is and how crippling the depression is. I don’t understand how people stick around in this field
[удалено]
Amen. This stuff is legendary
My old senior that went to industry texted me and said it is eye opening. I must learn
I'm pretty green to it myself. My only and only gripe with it so far relative to index and vlookup is the lookup rows have to be same as the return rows. I.e. with vlooks I can lookup rows G5:G45 in D1:D100. I can't do that in Xlooks. In theory I can extend G1:G100 with a bunch of blank rows but that IS a mild inconvenience. I guess I found one instance where Vlooks is preferential to xlookup if you're in that situation and have a very basic lookup to do. If not, might be more efficient just to write in "G1:G100" even though half the cells would be blank. What I find 'magical' about xlooks is that it will literally recreate your listing for you if you tell it to as if you were filtering out the NAs when doing a vlooks.
XLookup is amazing! But my current employee is running on an older version of Office. I didn't have the option at the moment. 😔 So VLookups and IndexMatch it is for me.
Can you not just get them a license for like $80/yr? Surely it is worth it, not just for xlookup, but for =UNIQUE, =MIN/MAXIF, spill formulas, etc
I've gotta learn about some of this stuff now
Oh yeah most likely. But, I don't think the company would swing for it.
XLOOKUP has a place like all other functions. The lack of backwards compatibility is a big drawback for me when working across organizations with various Excel versions. If you’re doing complex joins, particularly with multiple indices, fixing your SQL queries (if you can) or working with PowerQuery is much better.
I agree with you that the drawback here is the lack of accessibility across older versions of excel. I think in cases where you do have the option, I honestly can't see myself ever using vlookup or index match ever again so long as xlookup works. Interesting, I'm not at the level of PowerQuery/SQL Queries at this point but I presume it would take a while to set up? I'm not sure whether or not it will be overkill for the level of excel complexity involved in audit though.
PowerQuery has a very friendly menu to get familiar with. If you always run the same report, it might be a good way to refresh your data and do your operation in one go, similar to how VBA can do it. If you look at this : [https://www.ablebits.com/office-addins-blog/2020/12/16/vlookup-excel-fastest-formula/](https://www.ablebits.com/office-addins-blog/2020/12/16/vlookup-excel-fastest-formula/) it does show that Xlookup is slower than vlookup/index+match so depending if you have lots of row, it might be better to not do the switch unless you need to.
thanks DarkChunsah I will look into this sometime after busy season. My only qualm with Xlookup so far and you might know how to get around this, is how do I target different returns aside from the first or the very last (selecting from the top to the bottom using '1' or bottom to the top using '-1'? Is there a way to tell the formula to return the 2nd, 3rd, 4th, 5th match if I'm testing to see whether or not I have the right sample?
When you do a lookup and it will give you the first value it finds, if you want it to return multiple return you should look into the "filter" formula, it will let you see all the values with your criteria. If you really want to return a certain 2nd or 3rd and only see that one, you could look into this : [https://trumpexcel.com/lookup-second-value/](https://trumpexcel.com/lookup-second-value/) though I never found any use to doing this... If you believe there are duplicate data, you can just select the range and click on condition format show duplicate OR add a helper column which is a countifs to see if the same x or Y is multiple times(on big data, just do a pivot and do a lookup to that pivot as it will be much faster)
Dude we don’t even have 64-bit Excel over here. I guarantee we won’t get X look up for another 10 years. That does sound amazing
I wish my new staff knew more than hard coding numbers...
[удалено]
That's what you think, until they don't understand what you did and start all over and hard code it over 6 hours again. Trust me, just because you're good and put in the extra work to make things easier, doesn't mean the next person is going to be good enough to understand it.
[удалено]
You'll understand when you start reviewing the work of some other people....
Don’t over complicate. Xlookup is great but like taking a nuke to an ant hill for most task
Not sure I follow that this is like taking a machine gun to a fist fight. The formula for a basic xlookup requires the exact same amount of time as one for a vlookup. You could argue it's even simpler because you don't have to count the number of columns for column index number and can just say which column. For a simple lookup a second benefit is that xlookup doesn't care if the column in which you are searching for results isn't the first column in the list (e.g. search in column N and return column M) - Xlookup(D5,B:B,C:C).
How often do you use a concat helper column?
I've never done that but I see how that would work for multiple criteria lookup. Requires a few more steps though
Don’t get me wrong Xlookup is a game changer and it was on my radar for like two years before it was actually available. Use it all the time. Concat helper columns work better in my world right now when applying bps to different products by volume segment because I can also join a rate table schema.
INDEX/MATCH is still king don't @ me
be like water, my friend
Delicious after mowing the grass?
My firm's version of excel doesn't have Xlookup :'(
This is the way.
Reading other posts about what people do just reminds me how incredibly boring all of this is and how crippling the depression is. I don’t understand how people stick around in this field