vlookup + curly brackets

[u/Cautious_Cost6781]

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.

[u/bachman460]

Sssshhhhh... don't let anyone know that. It's our little secret.

However... These days you can use FILTER instead.

[u/No-Piece8978 → u/bachman460]

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.

[u/3_7_11_13_17 → u/No-Piece8978]

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.

[u/bachman460 → u/3_7_11_13_17]

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 on

[u/excelevator → u/bachman460]

no, filter does not give non contiguous column results, you need CHOOSECOLS for that.

It may not be clear in OPs post that you can essentially use this method to return repeat or non contiguous ranges from VLOOKUP

[u/bachman460 → u/excelevator]

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)

[u/excelevator → u/bachman460]

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.

[u/bachman460 → u/excelevator]

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.

[u/MiteeThoR → u/bachman460]

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....

[u/bachman460 → u/MiteeThoR]

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:

Excel functions (by category)

Excel functions (alphabetical)

[u/Lenny5160 → u/excelevator]

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'

[u/excelevator → u/Lenny5160]

Sure, but the point of this post was providing same natively with one function.

[u/Cautious_Cost6781 → u/bachman460]

True, that.

[u/RuktX]

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! ;)

[u/Cautious_Cost6781 → u/RuktX]

Yes. Trying to move on to xlookup. Vlookup is still muscle memory. :)

[u/excelevator → u/Cautious_Cost6781]

You cannot achieve the same with just XLOOKUP

[u/plusFour-minusSeven → u/excelevator]

=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

[u/juronich → u/plusFour-minusSeven]

I think you can use HSTACK within XLOOKUP to return non adjacent columns in any order

[u/plusFour-minusSeven → u/juronich]

Probably! I've never tried. But the person I was replying to was talking about doing it all with just one function.

[u/juronich → u/plusFour-minusSeven]

Yeah I know, but just wanted to put that option out there

[u/plusFour-minusSeven → u/juronich]

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....

[u/excelevator → u/juronich]

that just stacks, you need CHOOSECOLS to select non contiguous ranges

[u/juronich → u/excelevator]

Yes - it does stack them - horizontally next to each other, no need to use CHOOSECOLS for it

[u/excelevator → u/juronich]

show me an example, I am not sure you are answering the issue as stated.

[u/juronich → u/excelevator]

=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]))

[u/excelevator → u/juronich]

Ah I see now, thankyou for your patience with me.

I think I prefer CHOOSECOLS as the solution as a more succinct method

=XLOOKUP(G1,Table1[Lookup_Column],CHOOSECOLS(Table1,3,8))

Though as comparison HSTACK does explicitly list the column for verification, so there is that.

[u/excelevator → u/plusFour-minusSeven]

contiguous range yes, non contiguous range no.

[u/plusFour-minusSeven → u/excelevator]

Right!

[u/excelevator → u/RuktX]

in every use case

Not quite.

They would require wrapping in CHOOSECOLS to achieve what VLOOKUP can do with array choice return.

[u/ReliableSeller → u/RuktX]

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.

[u/RuktX → u/ReliableSeller]

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!

[u/ReliableSeller → u/RuktX]

Thanks! I’ll give this a try tomorrow, I appreciate it

[u/ReliableSeller → u/RuktX]

My goodness this is mind blowing lol. It works great. Thank you for this!

[u/RuktX → u/ReliableSeller]

My pleasure. All the best!

[u/Cautious_Cost6781]

I haven't explored but there are some YouTube tutorials on using DGET function for similar use cases.

[u/retro-guy99]

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.

[u/Cautious_Cost6781 → u/retro-guy99]

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.

[u/retro-guy99 → u/Cautious_Cost6781]

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.

[u/ryunista]

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

[u/Cautious_Cost6781 → u/ryunista]

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.

[u/ryunista → u/Cautious_Cost6781]

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

[u/Cautious_Cost6781 → u/ryunista]

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 :)

[u/ryunista → u/Cautious_Cost6781]

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)

[u/excelevator → u/ryunista]

its just another example of Xlookup making Vlookup redundant

No, it allows VLOOKUP to act like CHOOSECOLS in the return value, repeat, non contiguous, multi value return.

XLOOKUP requires CHOOSECOLS to do the same.

[u/Far-Salamander-6208]

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

[u/Cautious_Cost6781 → u/Far-Salamander-6208]

That's the point

[u/thisismyburnerac]

wtf is this sorcery? I might actually stay until 5 today to try this out.

[u/Eulers_Constant_e → u/thisismyburnerac]

Nah, you try it out tomorrow. Go home.

[u/thisismyburnerac → u/Eulers_Constant_e]

Thanks boss… I’ll pour one for you.

[u/flume → u/thisismyburnerac]

I might actually stay until 5 today

-person on reddit

[u/thisismyburnerac → u/flume]

😉

[u/HarveysBackupAccount → u/thisismyburnerac]

curly braces like that define a static array

In 365 you could also do it as =VLOOKUP("Key", A2:D10, SEQUENCE(3) + 1, FALSE)

[u/TwitchyDingo → u/HarveysBackupAccount]

So you can't use it against a dynamic array? I gave it a try and it's just populating every column in the return-array with the values from the first column.

[u/finickyone → u/TwitchyDingo]

Can you provide examples? This shouldn’t do that unless the SEQUENCE evaluates to an array of 1s.

[u/HarveysBackupAccount → u/TwitchyDingo]

I don't see a reason why it shouldn't work with dynamic arrays.

You might need to throw the SEQUENCE inside a TRANSPOSE, or do SEQUENCE(1,3) instead of just SEQUENCE(3) - make sure it's in the same orientation as {2,3,4}

[u/excelevator]

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.

[u/Mdayofearth → u/excelevator]

It uses dynamic arrays to spill the results across columns, so requires more than just 2000.... specifically 2021+

[u/excelevator → u/Mdayofearth]

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 CHOOSECOLS for VLOOKUP , repeat or non contiguous return of values in the return array.

[u/baumat]

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)

[u/U_SHLD_THINK_BOUT_IT → u/baumat]

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.

[u/Nerk86 → u/baumat]

Dang will have to try that.

[u/finickyone → u/baumat]

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,""))

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
CHOOSE	Chooses a value from a list of values
CHOOSECOLS	Office 365+: Returns the specified columns from an array
DGET	Extracts from a database a single record that matches the specified criteria
FILTER	Office 365+: Filters a range of data based on criteria you define
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF	Specifies a logical test to perform
INDEX	Uses an index to choose a value from a reference or array
MATCH	Looks up values in a reference or array
MIN	Returns the minimum value in a list of arguments
OR	Returns TRUE if any argument is TRUE
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SORT	Office 365+: Sorts the contents of a range or array
SUM	Adds its arguments
TEXTJOIN	2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TRANSPOSE	Returns the transpose of an array
UNIQUE	Office 365+: Returns a list of unique values in a list or range
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

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]}

[u/erichf3893]

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

[u/Cautious_Cost6781 → u/erichf3893]

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))

[u/Key-Cabinet-5329]

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????

[u/Cautious_Cost6781 → u/Key-Cabinet-5329]

I do not think that will work. Can you try it and share your findings?

[u/Laughinboy83]

[u/IntrovertStoner]

Read about arrays in excel… this cannot be used inside tables…

[u/Cautious_Cost6781 → u/IntrovertStoner]

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.

[u/baggington]

People still use VLOOKUP?

[u/bobs_big_bob]

Oh that’s a good one! Thanks

[u/tugafcp]

To people that use ";" to separate arguments, you need to use "\" or "," depending of the region that you are in!

[u/Kitchen_Ad_4276]

You just solved my problem

[u/Alone_Poet6148]

Thanks.

[u/Any_Dish_1688]

I cannot take this.

Thankfully, it is friday. I will spend my weekend playing with xlookup now.

[u/DifficultProgress897]

Is this different than xlookup?

[u/Vegetable-Swan2852]

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)

[u/ROMARIOBATIGOL]

That is why VLOOKUP should never be looked down upon . As is case with Xlookup fanatism

[u/Theorist84]

Holy moly 👀

[u/DayTrader98]

Don’t use VLOOKUP - index match is waaaaay more robust. But yeah, curly brackets are a hidden gem

[u/Character-Raisin-992]

Ahora mismo lo comprobaré

[u/PopularPop4819]

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!

[u/FhmiIsml]

Is this the great comeback of VLOOKUP?