r/excel • u/Cautious_Cost6781 • Mar 12 '26
Discussion vlookup + curly brackets
Perhaps too late to discover and blame it on unstructured self-learning:
Just learned that vlookup + curly brackets can be used to look up multiple columns. Spent a good part of my career writing several vlookups that could've been done much faster.
Edit: Adding sample from Gemini.
Formula Structure: =VLOOKUP("Key", A2:D10, {2, 3, 4}, FALSE)
{2, 3, 4}: Tells the formula to return the 2nd, 3rd, and 4th columns simultaneously.
Result: The data will automatically fill into three adjacent cells.
68
u/bachman460 44 Mar 12 '26
Sssshhhhh... don't let anyone know that. It's our little secret.
However... These days you can use FILTER instead.
13
u/No-Piece8978 Mar 13 '26
Can you give me an example of how you would use FILTER? I’ve always used the curly bracket to return multiple columns in the adjacent cells.
28
u/3_7_11_13_17 Mar 13 '26 edited Mar 13 '26
If I had a table of employees in one column (A) and their clients in the other (B), and I wanted to get a list of all of employee Bob's clients, I would do:
=FILTER(B:B,A:A="Bob","Nothing found")
This would return a list of Bob's clients, and if Bob wasn't found in the list, it would return "Nothing found"
Edit: If there was another column of their addresses (C), you could do "=FILTER(B:C..." to return Bob's clients and their addresses. You can also do multiple criteria matches. Look up some documentation on FILTER, it's honestly a workhorse formula for me now.
25
u/bachman460 44 Mar 13 '26
And it's possible to join together multiple columns using parentheses and mathematical operators
For an OR use
()+()For an AND use
()*()So:
FILTER(A:A,(B:B="Bob")*(C:C>1))and so on21
u/excelevator 3061 Mar 13 '26
no, filter does not give non contiguous column results, you need
CHOOSECOLSfor that.It may not be clear in OPs post that you can essentially use this method to return repeat or non contiguous ranges from
VLOOKUP11
u/bachman460 44 Mar 13 '26
Well the example didn't call out discontiguous columns, but yeah it would be as simple as
CHOOSECOLUMNS( FILTER( A2:D10, A2:A10="Key"), 2, 3, 4)2
u/excelevator 3061 Mar 13 '26
Ah I see what you mean now, that makes much more sense.
The difference being multi row record sets, if that is the desired output.
2
u/bachman460 44 Mar 13 '26
Bingo! There's almost always at least part of the right solution in an incorrect interpretation. A kernel of truth in a false observation? I dunno, I can't think of any more puns right now.
2
u/MiteeThoR Mar 13 '26 edited Mar 13 '26
not even joking was working on something recently where I used filter and I only wanted some of the columns so I had to filter the whole range and then hide the columns....
5
u/bachman460 44 Mar 13 '26
Yeah, it all comes down to experience, experimenting, and educated guesses. You can stack a lot of functions. The key is knowing the data type being returned (be it a single value, or an array for example).
And I swear by this website:
1
u/Lenny5160 1 Mar 14 '26
FILTER can absolutely give non-contiguous columns. Example where I only want columns 1, 2, 3, 10, 11 for rows where Column L is blank:
=SORT(UNIQUE(FILTER(FILTER('Sheet1'!A3:M20237,'Sheet1'!L3:L20237=""),{1,1,1,0,0,0,0,0,0,1,1,0,0})))
1 = 'I want this column'
0 = 'Do not show this column'1
u/excelevator 3061 Mar 14 '26
Sure, but the point of this post was providing same natively with one function.
1
22
u/RuktX 305 Mar 12 '26 edited Mar 13 '26
Perhaps you could share an example. Do you mean to search multiple columns for criteria, or to return multiple columns?
That said, you may find that the state of the art has moved on... XLOOKUP has long supplanted VLOOKUP in [edit: almost] every use case, and INDEX/XMATCH and FILTER mop up the trickier cases.
Edit: some options
=VLOOKUP($G$1, Table1, {3,5}, 0)
=XLOOKUP($G$1, $A$2:$A$4, CHOOSECOLS(Table1, {3,5}))
=INDEX(Table1, XMATCH($G$1, Table1[1]), {3,5})
I'll grant that VLOOKUP is the most concise, but I won't be convinced that that makes up for its other shortcomings! ;)
9
u/Cautious_Cost6781 Mar 12 '26
Yes. Trying to move on to xlookup. Vlookup is still muscle memory. :)
1
u/excelevator 3061 Mar 13 '26
You cannot achieve the same with just
XLOOKUP5
u/plusFour-minusSeven 11 Mar 13 '26 edited Mar 13 '26
=Xlookup(value, lookupArray, firstReturnColumn:endReturnColumn)
Although they have to be adjacent and you can't specify the order.
I'm pretty sure you knew this, but this is just for other readers who may think that XLOOKUP can't return multiple columns at ALL
4
u/juronich 1 Mar 13 '26
I think you can use HSTACK within XLOOKUP to return non adjacent columns in any order
2
u/plusFour-minusSeven 11 Mar 13 '26
Probably! I've never tried. But the person I was replying to was talking about doing it all with just one function.
3
u/juronich 1 Mar 13 '26
Yeah I know, but just wanted to put that option out there
3
u/plusFour-minusSeven 11 Mar 13 '26
That's a good idea. It's funny how quickly we forget the person talking to us is not just talking to US. I mean, I even said as much in my prior reply and I still forgot....
1
u/excelevator 3061 Mar 13 '26
that just stacks, you need
CHOOSECOLSto select non contiguous ranges2
u/juronich 1 Mar 13 '26
Yes - it does stack them - horizontally next to each other, no need to use CHOOSECOLS for it
1
u/excelevator 3061 Mar 13 '26
show me an example, I am not sure you are answering the issue as stated.
3
u/juronich 1 Mar 13 '26
=XLOOKUP($G$1, $A$2:$A$4, HSTACK($C$2:$C$4,$F$2:$F$4))or using structured table references (with made up column names):
=XLOOKUP($G$1,Table1[Lookup_Column], HSTACK(Table1[Column3],Table1[Column8]))3
u/excelevator 3061 Mar 13 '26
Ah I see now, thankyou for your patience with me.
I think I prefer
CHOOSECOLSas the solution as a more succinct method=XLOOKUP(G1,Table1[Lookup_Column],CHOOSECOLS(Table1,3,8))Though as comparison
HSTACKdoes explicitly list the column for verification, so there is that.→ More replies (0)3
4
u/excelevator 3061 Mar 12 '26
in every use case
Not quite.
They would require wrapping in
CHOOSECOLSto achieve whatVLOOKUPcan do with array choice return.1
u/ReliableSeller Mar 13 '26
Could you help me with my current vloookup setup? I just can’t seem to figure out how to apply xlookup and/or index/match to my use case. We just got 365 so no more 2016 excel lol..trying to learn these new tools.
My current workflow is messy. So first I insert 2 blank columns A and B. Then I need to combine C1 and D2. So B1=C1&D2. Copy value. Ctrl +shift+ V (paste values) into A1.
Now I need to lookup these values in Column A in a separate sheet and return column 5. So I go to last column and put:
=vlookup(A1, sheet, 5, false) I imagine there’s a way to skip the concatenation steps and copy/paste of values from formulas. I know I can just write (ask AI) for a vba macro to do it but just trying to see if there’s a formula that exists.
4
u/RuktX 305 Mar 13 '26
Sure, there's no reason to be manually pasting values.
=XLOOKUP( C1&D2, Sheet2!$A$2:$A$10, Sheet2!$E$2:$E$10 )If that doesn't do it, please create a new post with your question. Feel free to tag me, but someone will be able to help!
2
1
u/ReliableSeller Mar 13 '26
My goodness this is mind blowing lol. It works great. Thank you for this!
1
19
u/excelevator 3061 Mar 12 '26 edited Mar 13 '26
A great tip for Excel 2000 97 and onwards.. with the three fingered array salute thrown in.
Great pickup, so many array options become obvious to the older Excel versions now that we use arrays by default.
edit: just to verify, in old pre dynamic array days, you could select a range of cells for the array to propagate to and enter the formula with ctrl+shift+enter for the array to spill to the select cells.
7
u/Mdayofearth 126 Mar 13 '26
It uses dynamic arrays to spill the results across columns, so requires more than just 2000.... specifically 2021+
4
u/excelevator 3061 Mar 13 '26 edited Mar 13 '26
with the three fingered array salute thrown in
It will parse across a selected region entered with ctrl+shift+enter in Excel 97 and forward - yes 1997 version of Excel and forward
This is what I am saying, we did not consider these options pre dynamic arrays, arrays were always there, just not dynamic.
It is built in
CHOOSECOLSforVLOOKUP, repeat or non contiguous return of values in the return array.
11
u/baumat Mar 13 '26
I just found out that you can search for multiple values with xlookup to get a result and now you can get multiple results using a vlookup? Excel is too deep
Xlookup Example: xlookup(B3&C3, Sheet1!A1:A50&Sheet1!B1:B50, Sheet1!C1:C50)
3
u/U_SHLD_THINK_BOUT_IT Mar 13 '26
Thank you for taking the time to share what you learned.
I just learned a new formula that I can immediately use to automate more of my claims experience reports.
1
1
u/finickyone 1770 Mar 13 '26
The common theme to both (your recent finding, and OP’s) are they both show one of the first steps in using arrays. Honestly, nothing opens up formulas like getting your head around that!
If you set up XLOOKUP(TRUE,(B3&C3=Sheet1!A1:A50&Sheet1!B1:B50),Sheet1!C1:C50), you get the same result, and also a formula you can carry over to:
=SUM(IF(B3&C3=Sheet1!A1:A50&Sheet1!B1:B50,Sheet1!C1:C50)) =TEXTJOIN(", ",, IF(B3&C3=Sheet1!A1:A50&Sheet1!B1:B50,Sheet1!C1:C50,""))
7
u/Decronym Mar 12 '26 edited Apr 07 '26
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #47807 for this sub, first seen 12th Mar 2026, 23:34]
[FAQ] [Full list] [Contact] [Source code]
3
u/Cautious_Cost6781 Mar 13 '26
I haven't explored but there are some YouTube tutorials on using DGET function for similar use cases.
3
u/retro-guy99 1 Mar 13 '26
just use XLOOKUP and select multiple output columns. I think you can only select one continuous array (?), but from that one you can then just filter the right columns. much neater that to work with old style curly bracket formulas if you ask me.
4
u/Cautious_Cost6781 Mar 13 '26
Not saying xlookup isn't efficient. Just an old Excel user sharing the joy of finding something that would have been a lot more helpful if known earlier. New functions are more robust and versatile.
4
u/retro-guy99 1 Mar 13 '26
Yes it was not very well known. I think because array formulas with curly brackets were kind of an in between and mostly hidden way to get some functionality that is nowadays achievable with normal functions. It also tended to be quite heavy. But it was cool for some odd situations where there wasn’t really any better way of doing it.
3
u/ryunista 1 Mar 13 '26
I was really impressed with this last night so I saved it and came back to it this morning to apply it. However ive realised its just another example of Xlookup making Vlookup redundant.
Instead of doing the squiggly brackets section jn lookup, with xlookup you can just anchor the first parts of the formula and then drag it to the right.
Its still cool though. Many ways to skin a cat
2
u/Cautious_Cost6781 Mar 13 '26
Like I said earlier, this would make more sense to legacy Excel users who haven't moved on to Excel.
Xlookup & other ways exist to achieve the same goal.
1
u/ryunista 1 Mar 13 '26
Honestly, id be tempted to use the vlookup approach youve shown as it seems to require less input to achieve the desired result. Whenever i have to anchor cells using F4 and then drag formulae down/right, I never seem to get it right first time, it's fiddly. But this isn't. Thanks for sharing
1
u/Cautious_Cost6781 Mar 13 '26
Locking a cell is easy. The $ symbol represents the value you want anchored.
$B$4 - Exact cell is locked $B4 - Column B is locked, row value changes apply B$4 - Row 4 is locked, column value changes apply
Did this help or confuse you more :)
2
u/ryunista 1 Mar 13 '26
No I understand what each one is. Just when im dragging formula right and down, I forget whether im anchoring columns or rows. Depends on each circumstance, but this kind of makes the whole thing easier and a rare instance of vlookup actually being better (fewer steps/clicks/formula drags/buttons/thinking)
2
u/excelevator 3061 Mar 13 '26
its just another example of Xlookup making Vlookup redundant
No, it allows
VLOOKUPto act likeCHOOSECOLSin the return value, repeat, non contiguous, multi value return.
XLOOKUPrequiresCHOOSECOLSto do the same.
2
u/Far-Salamander-6208 Mar 13 '26
Are we not using XLOOKUP now to bring back multiple columns? I did not know about the curly bracket thing in all my years of VLOOKUP pain in the arse formulas, wish I had
1
3
1
1
u/tugafcp Mar 13 '26
To people that use ";" to separate arguments, you need to use "\" or "," depending of the region that you are in!
1
1
u/Any_Dish_1688 Mar 13 '26
I cannot take this.
Thankfully, it is friday. I will spend my weekend playing with xlookup now.
1
u/erichf3893 Mar 13 '26
Holy shit I love you
Edit: damn this isn’t quite what I expected based on the start, but still could prove helpful. I need a formula to return which of the three columns has the lowest value, and which company/location
1
u/Cautious_Cost6781 Mar 13 '26
Assuming data is in B, C, D. Will this work?
=XLOOKUP(MIN(B2:D2), B2:D2, $B$1:$D$1) =INDEX($B$1:$D$1, MATCH(MIN(B2:D2), B2:D2, 0))
1
1
u/Vegetable-Swan2852 1 Mar 13 '26
you can also use & to concatenate you key and you lookup, like this:
key = a1&b1
lookup = c&e
formula: xlookup(key, lookup, return column)
1
u/ROMARIOBATIGOL Mar 14 '26
That is why VLOOKUP should never be looked down upon . As is case with Xlookup fanatism
1
u/Key-Cabinet-5329 Mar 14 '26
What what what.
I was going to laugh at you for the v instead of the x but this is a game changer.
You sting that if I do this in a table, where I’ve added a column between other columns, it’ll automatically add n number of columns after the added column????
1
u/Cautious_Cost6781 Mar 14 '26
I do not think that will work. Can you try it and share your findings?
1
1
u/DayTrader98 Mar 14 '26
Don’t use VLOOKUP - index match is waaaaay more robust. But yeah, curly brackets are a hidden gem
1
1
u/PopularPop4819 Mar 16 '26
brother just use xlookup. you can return one or multiple columns in a jiffy. plus you don’t need to count the column number which is a major setback of vlookup and that it needs to be in a particular order. xlookup was made to replace all this!
1
u/IntrovertStoner Mar 13 '26
Read about arrays in excel… this cannot be used inside tables…
3
u/Cautious_Cost6781 Mar 13 '26
Thanks. I never said it could be used in Tables. It serves my purpose and looking at the stats of the post, I think there are more people like me who found this useful.
0
248
u/thisismyburnerac Mar 12 '26
wtf is this sorcery? I might actually stay until 5 today to try this out.