Problem with Table references

[excelos]

Hello

I used the formula below and it works OK but it takes too many resources to calculate. I assume using Tables will prevent that as it will not have to search across the whole sheet since it will only search across the Table.
=TEXTJOIN(CHAR(10),,FILTER(Sheet1!$I:$I,(ISNUMBER(SEARCH([@[ID]],Sheet1!F:F)))*(Sheet1!B:B<=[@[Date 2]])*(OR(ISBLANK(Sheet1!D:D),Sheet1!D:D>=[@[Date 2]]))))

I tried to use Table references to make it work but it doesn't seem to work:
=TEXTJOIN(CHAR(10),,FILTER(Table2[@Text],(ISNUMBER(SEARCH([@[ID]],Table2[@LB])))*(Table2[@[Date 1]]<=[@[Date 2]])*(OR(ISBLANK(Table2[@[Date 3]]),Table2[@[Date 3]]>=[@[Date 2]]))))

Any idea why it does not work?

I think the problem is at the Table2[@Text], because it seems to refer to a specific row under this column, instead of the whole column.

However, I am not sure how to make it consider the whole column instead.

Thanks!

 

[Fluff]

Try it like

=TEXTJOIN(CHAR(10),,FILTER(Table2[Text],(ISNUMBER(SEARCH([@[ID]],Table2[LB])))*(Table2[[Date 1]]<=[@[Date 2]])*(OR(ISBLANK(Table2[[Date 3]]),Table2[[Date 3]]>=[@[Date 2]]))))

 

[excelos]

Thanks it works!

Can I ask a couple of points please:

1) What is the difference between Table2[LB] and Table2[[Date 1]]? Why double brackets?
2) Why when I copy the result, I get "text text" and not simply text text without the quotes?

Thanks!

 

[Fluff]

Why double brackets?

I just removed the @ signs which shouldn't be there & forgot to get rid of the extra brackets.
What do you mean by "copy the results"?

 

[excelos]

I just removed the @ signs which shouldn't be there & forgot to get rid of the extra brackets.
What do you mean by "copy the results"?

Thanks, but what is the difference between Table2[Date 1] and [@[Date 1]] ?

Copy the results = select cell and click CTRL+C then paste in Notepad. It includes the texts in quotes "".

 

[Fluff]

For info on structured references, have a look here Structured references in Excel tables
As for copying to notepad I have no idea. I've never done it.