An awesome trick when you are referring to a data type cell in a formula. I learned this from Chandoo - when he was explaining why another formula was *not* working.
Using XLOOKUP (or INDEX/MATCH), if your lookup table contains data types, you can refer to columns that you can't see in the grid.
I sort of new something related to this in the past - when you can sort a table by a field that is not in the table if the table contains data types.
Using XLOOKUP (or INDEX/MATCH), if your lookup table contains data types, you can refer to columns that you can't see in the grid.
I sort of new something related to this in the past - when you can sort a table by a field that is not in the table if the table contains data types.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2332.
XLOOKUP into Data Types. Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.This one is crazy.
I learned this inadvertently from Chandoo.
Chandoo was trying to explain to someone why XLOOKUP wasn't working when they were looking up data types.
And I said "Holy smokes!" That means that if we have a column of data types like over there in column A.
Just countries and they've all been converted to geography using Data, Data Types.
That means that you would be able to do an XLOOKUP into that column without anything else being in that table like this.
If I want to know the population of Brazil: so I asked for XLOOKUP of Brazil. Where are we looking?
Okay, well that table is called Countries. So, Countries and then the country column.
But i'm going to further define that and say dot Name, like that.
And then, what do I want to return?
Well the same thing: I want Countries, Country dot population.
Now, for this to work -- of course we don't need a fourth argument because it's an exact match -- for this to work that list of countries has to be made into a Table over there on the left hand side. We double click to copy this down.
We get the population for each country! That's pretty wild.
That means that the cell contains an entire array of information.
Using this cool syntax that Chandoo offered with the Countries Country.Name and Country's Country.Population means that we can access that information without having it appear in the grid.
It's not just XLOOKUP. There's nothing magic about XLOOKUP.
If we went back to INDEX and MATCH.
You could do it so the INDEX of the Population, which one do we want?
We want the match of Brazil into the names and an exact match and that works .
It seems to be related to something i knew in the past.
Here's a bunch of cities that have been converted to geography and because i made this into a table you can open this little drop down here and choose something.
I want to sort by latitude. This would make the cities arrange from north to south.
i'll say Sort Largest to Smallest and Reykjavik in Iceland is the furthest north and then go down to Auckland New Zealand is the further south of these cities.
So I kind of knew there was something special about having a table with Data Types.
It is cool that it works.
The question that came up...
There we have Angola here as a geography data type and just Angola. Are those equal? C5 equals A5?
No they're not.
It's just like if we had a number 12 and a text 12. Two different data types they're not equal. C8 equals C9?
No, they're not.
So that's that's kind of what's going on. Pretty wild.
I thought you might enjoy that. I knew that I was pretty intrigued by it.
If you like these tricks please Subscribe and ring that bell.
Feel free to post any comments down in the comments below.
Check out my new book MrExcel 2020 Seeing Excel Clearly.
Click that i in the top right hand corner for more information.
Well hey thanks to Chandoo for that awesome tip. Thanks to you for stopping by.
We'll see you next time for another Netcast from MrExcel.
XLOOKUP into Data Types. Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.This one is crazy.
I learned this inadvertently from Chandoo.
Chandoo was trying to explain to someone why XLOOKUP wasn't working when they were looking up data types.
And I said "Holy smokes!" That means that if we have a column of data types like over there in column A.
Just countries and they've all been converted to geography using Data, Data Types.
That means that you would be able to do an XLOOKUP into that column without anything else being in that table like this.
If I want to know the population of Brazil: so I asked for XLOOKUP of Brazil. Where are we looking?
Okay, well that table is called Countries. So, Countries and then the country column.
But i'm going to further define that and say dot Name, like that.
And then, what do I want to return?
Well the same thing: I want Countries, Country dot population.
Now, for this to work -- of course we don't need a fourth argument because it's an exact match -- for this to work that list of countries has to be made into a Table over there on the left hand side. We double click to copy this down.
We get the population for each country! That's pretty wild.
That means that the cell contains an entire array of information.
Using this cool syntax that Chandoo offered with the Countries Country.Name and Country's Country.Population means that we can access that information without having it appear in the grid.
It's not just XLOOKUP. There's nothing magic about XLOOKUP.
If we went back to INDEX and MATCH.
You could do it so the INDEX of the Population, which one do we want?
We want the match of Brazil into the names and an exact match and that works .
It seems to be related to something i knew in the past.
Here's a bunch of cities that have been converted to geography and because i made this into a table you can open this little drop down here and choose something.
I want to sort by latitude. This would make the cities arrange from north to south.
i'll say Sort Largest to Smallest and Reykjavik in Iceland is the furthest north and then go down to Auckland New Zealand is the further south of these cities.
So I kind of knew there was something special about having a table with Data Types.
It is cool that it works.
The question that came up...
There we have Angola here as a geography data type and just Angola. Are those equal? C5 equals A5?
No they're not.
It's just like if we had a number 12 and a text 12. Two different data types they're not equal. C8 equals C9?
No, they're not.
So that's that's kind of what's going on. Pretty wild.
I thought you might enjoy that. I knew that I was pretty intrigued by it.
If you like these tricks please Subscribe and ring that bell.
Feel free to post any comments down in the comments below.
Check out my new book MrExcel 2020 Seeing Excel Clearly.
Click that i in the top right hand corner for more information.
Well hey thanks to Chandoo for that awesome tip. Thanks to you for stopping by.
We'll see you next time for another Netcast from MrExcel.
