2

In Excel, I have a function that outputs filtered contents from several tables that are combined using VSTACK().

As can be seen in the example of what I have now, this works but only outputs the 'Name'. Is it possible to instead output 'Name Surname' in the same column, as in the example of what I wish to have?

What I have now.

=LET(v, VSTACK(TableA, TableB), FILTER(INDEX(v, , 1), INDEX(v, , 3)=$I$2, "No students at this result"))

enter image description here

What I wish to have.

enter image description here

I have tried to simple CONCAT() the columns that I wish to output, but the FILTER() function accepts an array as it's first argument, so this fails.

1 Answer 1

3

Instead of CONCAT() use Ampersand --> & to combine two arrays like as shown in the example:

enter image description here


=LET(
     v, VSTACK(TableA, TableB),
     FILTER(INDEX(v, , 1)&" "&INDEX(v,,2), INDEX(v, , 3)=$I$2, "No students at this result"))

Also since you have access to VSTACK() then use TAKE() function to make the formula bit shorter:

=LET(
     _v,VSTACK(TableA,TableB),
     FILTER(TAKE(_v,,1)&" "&INDEX(_v,,2),TAKE(_v,,-1)=I2,"No students at this result"))

Another way is passing a custom LAMBDA() calculations using BYROW() function :

=LET(
     _v,VSTACK(TableA,TableB),
     _f, FILTER(TAKE(_v,,2),TAKE(_v,,-1)=I2,"No students at this result"),
     BYROW(_f,LAMBDA(x, TEXTJOIN(" ",1,x))))

But note here is caveat when using TEXTJOIN() function, as per MSFT Documentations if the resulting string exceeds 32767 characters (cell limit), returns the #VALUE! error.


2
  • 1
    Thanks for your answer, & does exactly what I was looking for. However, I don't think I can use TAKE() as in reality I have more than 3 columns, and 'Result' is somewhere in the middle. But useful to know of it's existence.
    – David Gard
    Mar 4 at 12:35
  • @DavidGard ah ok, I wasn't aware of that, but thought it would be useful. Then INDEX() is your best bet. Thanks btw! Also there are few new functions like CHOOSECOLS() or CHOOSEROWS() then TEXTBEFORE() & TEXTAFTER() Mar 4 at 12:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .