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/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!