Another question from the UCF Accounting Conference. JoAnne asks... XLOOKUP can return multiple columns. What if you want to return several non-adjacent columns, for example, January, April, July, October?
There are three solutions presented here.
Table of Contents
(0:00) XLOOKUP return non-adjacent columns
(0:30) XLOOKUP with FILTER
(2:39) Using HSTACK
(3:17) VLOOKUP with Ctrl+Shift+Enter
(4:40) How would you solve it?
There are three solutions presented here.
Table of Contents
(0:00) XLOOKUP return non-adjacent columns
(0:30) XLOOKUP with FILTER
(2:39) Using HSTACK
(3:17) VLOOKUP with Ctrl+Shift+Enter
(4:40) How would you solve it?
Transcript of the video:
Is it possible using XLOOKUP to return multiple columns?
Yes, but columns that are not adjacent?
Another question from the UCF Accounting seminar last week. This one from Joanne.
For example, if we have months, January through December, can we return just January, April, July, and October?
There are basically three different solutions to this. I'm interested to hear what you have down below.
There might be a much better way to go than what I try to do here.
So the very first thing I want to do is I'm going to use an XLOOKUP.
But when I get to the part of the XLOOKUP, as far as what to return.
Instead of getting this whole purple range over here, which would give us all 12 columns.
I'm going to FILTER that purple range and I'm going to do a horizontal FILTER.
That's something that the regular filter in Excel can't do - the horizontal FILTER.
And in curly braces here, I'm going to put what to do for I, what to do for J, what to do for K.
So it's True for I, and then False, False and we just repeat that True, False, False, True, False, False, True, False, False.
And that awesome little formula right there returns just the columns that we want.
So this column, this column, this column, this column.
I drop that into the “what to return” of XLOOKUP and we get this really long answer. Now the first couple of ideas here.
Alternates are just simplifying that a little bit. Instead of True and False, put ones and zeros.
One is true, zero's false. And then I started to think about it.
We have these extra two zeros at the end, which are not going to be returned.
So let's just leave that out of the equation altogether.
So leave out November and December then I'll won’t have to put the extra zero, zero at the end, saves me a couple of keystrokes.
I was just on a practice call for next week's Financial Modeling World Cup with Oz du Soleil and we were joking about the MOD function.
So I decided to dust off the MOD function.
We could put in the numbers three through 14 here using a clever SEQUENCE function. And then come out of that divided by three.
This gives me the remainder and then check to see if it's equal to zero.
Right?
So rather than typing 1 0 0 1 0 0, this clever MOD of SEQUENCE will basically give me the numbers: 1 0 0 1 0 0 1 0 0.
Doesn't make it any easier to understand. Just a little bit geekier.
Or, here's a really good idea and this one is super flexible.
Just somewhere out of the way, type the numbers one and zero.
That way, if later it changes and we have to do February and May and August and November, who knows why?
Then in our XLOOKUP there for the FILTER we just point to that range and that becomes which columns we want.
All of those essentially are the same using FILTER.
The next option and this is only if you are an Office Insider's beta right now because that's where HSTACK is.
The HSTACK function here allows us to stack four vectors together.
So we have the January vector, the April vector, the July vector, the October vector.
And it smashes it together into a single array and then here in our XLOOKUP, when we get to the point where, what do we want to return? We put that HSTACK in.
So go look for Andy in this column, when you find it, give me the HSTACK of columns I L O and R.
All right and then the last one, this is old school.
You don't need Office Insider. You don't need Microsoft 365.
Joe McDaid said we would never have to press Ctrl+Shift+Enter again but I'm going to press Ctrl+Shift+Enter.
So in order to use this old school array formula, we have to select the four cells where the answer is going to go.
And here's our good old friend VLOOKUP.
Go look up Andy and specify the whole table here, like that. Press the F4 to lock that down.
And then this is the point where we get to specify an integer for which columns do we want to return?
And again, I'm going to create an array of constants.
So inside the curly braces, I'm going to type 2 comma, 5 comma, 8 comma, 11 close the curly braces.
We still need to do the comma False at the end of the VLOOKUP. But don't press Enter.
Instead these old style array formulas I'm going to hold down Control and Shift with my left hand and press Enter with my right hand.
In the formula bar, it wraps the whole thing in curly braces.
Those curly braces were the old school way of telling us that I held down Ctrl+Shift+Enter to enter that formula as an array.
But the advantage of this is it works, going back to Excel 2016, 2013, 2010.
I mean probably all the way back to Excel 97.
This would work so the old school Ctrl+Shift+Enter. Now, this is one of those questions.
It was asked live in the seminar and I was going to go straight to HSTACK the newest, shiniest, coolest way to go.
I'm a lot happier here with this VLOOKUP because it'll work further back.
The whole hassle is you just have to learn when to Ctrl+Shift+Enter.
But I'm convinced that someone watching this has something much better, faster, easier. So please feel free down in the YouTube comments.
I'll tell Joanne to watch this video but also to come back a couple of days from now where there'll be much better ideas than what I had down in the YouTube comments.
I want to thank Sean at UCF for inviting me out for their annual accounting conference.
I want to thank Joanne for asking this question and I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
Now, if you love Excel, check out my new courses on the retrieve platform.
They're video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It's a super fast way to learn.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Yes, but columns that are not adjacent?
Another question from the UCF Accounting seminar last week. This one from Joanne.
For example, if we have months, January through December, can we return just January, April, July, and October?
There are basically three different solutions to this. I'm interested to hear what you have down below.
There might be a much better way to go than what I try to do here.
So the very first thing I want to do is I'm going to use an XLOOKUP.
But when I get to the part of the XLOOKUP, as far as what to return.
Instead of getting this whole purple range over here, which would give us all 12 columns.
I'm going to FILTER that purple range and I'm going to do a horizontal FILTER.
That's something that the regular filter in Excel can't do - the horizontal FILTER.
And in curly braces here, I'm going to put what to do for I, what to do for J, what to do for K.
So it's True for I, and then False, False and we just repeat that True, False, False, True, False, False, True, False, False.
And that awesome little formula right there returns just the columns that we want.
So this column, this column, this column, this column.
I drop that into the “what to return” of XLOOKUP and we get this really long answer. Now the first couple of ideas here.
Alternates are just simplifying that a little bit. Instead of True and False, put ones and zeros.
One is true, zero's false. And then I started to think about it.
We have these extra two zeros at the end, which are not going to be returned.
So let's just leave that out of the equation altogether.
So leave out November and December then I'll won’t have to put the extra zero, zero at the end, saves me a couple of keystrokes.
I was just on a practice call for next week's Financial Modeling World Cup with Oz du Soleil and we were joking about the MOD function.
So I decided to dust off the MOD function.
We could put in the numbers three through 14 here using a clever SEQUENCE function. And then come out of that divided by three.
This gives me the remainder and then check to see if it's equal to zero.
Right?
So rather than typing 1 0 0 1 0 0, this clever MOD of SEQUENCE will basically give me the numbers: 1 0 0 1 0 0 1 0 0.
Doesn't make it any easier to understand. Just a little bit geekier.
Or, here's a really good idea and this one is super flexible.
Just somewhere out of the way, type the numbers one and zero.
That way, if later it changes and we have to do February and May and August and November, who knows why?
Then in our XLOOKUP there for the FILTER we just point to that range and that becomes which columns we want.
All of those essentially are the same using FILTER.
The next option and this is only if you are an Office Insider's beta right now because that's where HSTACK is.
The HSTACK function here allows us to stack four vectors together.
So we have the January vector, the April vector, the July vector, the October vector.
And it smashes it together into a single array and then here in our XLOOKUP, when we get to the point where, what do we want to return? We put that HSTACK in.
So go look for Andy in this column, when you find it, give me the HSTACK of columns I L O and R.
All right and then the last one, this is old school.
You don't need Office Insider. You don't need Microsoft 365.
Joe McDaid said we would never have to press Ctrl+Shift+Enter again but I'm going to press Ctrl+Shift+Enter.
So in order to use this old school array formula, we have to select the four cells where the answer is going to go.
And here's our good old friend VLOOKUP.
Go look up Andy and specify the whole table here, like that. Press the F4 to lock that down.
And then this is the point where we get to specify an integer for which columns do we want to return?
And again, I'm going to create an array of constants.
So inside the curly braces, I'm going to type 2 comma, 5 comma, 8 comma, 11 close the curly braces.
We still need to do the comma False at the end of the VLOOKUP. But don't press Enter.
Instead these old style array formulas I'm going to hold down Control and Shift with my left hand and press Enter with my right hand.
In the formula bar, it wraps the whole thing in curly braces.
Those curly braces were the old school way of telling us that I held down Ctrl+Shift+Enter to enter that formula as an array.
But the advantage of this is it works, going back to Excel 2016, 2013, 2010.
I mean probably all the way back to Excel 97.
This would work so the old school Ctrl+Shift+Enter. Now, this is one of those questions.
It was asked live in the seminar and I was going to go straight to HSTACK the newest, shiniest, coolest way to go.
I'm a lot happier here with this VLOOKUP because it'll work further back.
The whole hassle is you just have to learn when to Ctrl+Shift+Enter.
But I'm convinced that someone watching this has something much better, faster, easier. So please feel free down in the YouTube comments.
I'll tell Joanne to watch this video but also to come back a couple of days from now where there'll be much better ideas than what I had down in the YouTube comments.
I want to thank Sean at UCF for inviting me out for their annual accounting conference.
I want to thank Joanne for asking this question and I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
Now, if you love Excel, check out my new courses on the retrieve platform.
They're video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It's a super fast way to learn.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
oh the scalability...
