Excel XLOOKUP Return Non Adjacent Columns - 2488

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on May 18, 2022 .
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?
maxresdefault.jpg


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.
 

datatronics505

New Member
Joined
Nov 26, 2022
Messages
42
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
So, to eliminate columns from consideration for a match during a lookup one would... type "false" for each of those columns... and pass that list to a function... o_O oh the scalability...
 

rorymac

New Member
Joined
Nov 24, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi MrExcel, (aka MessiExcel ;))

I posted a question similar, and thinking there must be a simpler way, has anyone thought of one or have you discovered it.

If the data is a within a table and looking for a match in another table then I thinking you know column header, so could it work like filter function and return from the array based on the new table headings? So generate a new table with January, April, July, and October as column headers and the great lookup function returns from another table based on the column header some how.. Means you just need to change the column header of the new table to extract the required data. I thought it might be that I was not using the right syntax..

I have not used cube functions, could this be used? I going to look into xstack as well, but I am not a excel expert, just try and error, search web, try and error, search web, get frustrated.. :biggrin:

Link to my similar question
 

Forum statistics

Threads
1,187,001
Messages
5,961,049
Members
438,516
Latest member
Fintrics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top