I’ll try to simplify (the actual spreadsheet has way more fields than this simplified version):
-
Sheet 1: “Clients” - 5 columns (ID, Name, Surname, Age, Phone no.)
-
Sheet 2: “Summary” - 2 columns (Client ID, Summary)
What I’d like to do is: upon entering a client ID in sheet 2, I would like to have the “Summary” field populate itself by concatenating some of the client’s data in sheet 1, in this case Name, Surname and Phone no. .
I know I can get the row ID of the entered Client ID in sheet 1 by using MATCH() (MATCH($A1; Clients.$A:$A; 0)), I know I can pick a record from a range (in this case let’s say B to C, Name and Surname) using INDEX() (INDEX(Clients.$B:$C; MATCH($A1; Clients.$A:$A; 0))) and I know I can join the resulting row’s fields using TEXTJOIN(). So my final formula currently looks like this:
=TEXTJOIN(" "; 1; INDEX(Clients.$B:$C; MATCH($A1; Clients.$A:$A; 0)))
This works perfectly and correctly joins the Name and Surname of the entered client ID using a space.
However the problem arises when I try to join column E to the result. Since this column is two columns apart from column C, I can’t simply extend the range B to C to include E, otherwise I would get an unwanted column D in the result.
According to the documentation, multiple ranges may be joined while calling INDEX() using a tilde character. So I tried both:
=TEXTJOIN(" "; 1; INDEX(Clients.$B:$C~Clients.$E:$E; MATCH($A1; Clients.$A:$A; 0)))
and
=TEXTJOIN(" "; 1; INDEX((Clients.$B:$C~Clients.$E:$E); MATCH($A1; Clients.$A:$A; 0)))
(the difference being just that the ranges to be joined are enclosed in parenthesis in the second formula)
in order to join column E to the range B to C.
This doesn’t work, despite Calc correctly highlighting range B to C and column E when editing the formula. Only fields in the range B to C are correctly sourced and displayed, the field in column E is flat out missing.
I’ve tried just indexing the range E to E to see if the problem was with the column itself, but in that case the range is correctly sourced and its field displayed. I’ve tried extending the range B to C up to E without separately joining the range E to E and again that works perfectly, but again, column D is unwanted in this case.
I know I could probably get away with using multiple INDEX()es and join the result toghether, however this formula is supposed to be run on a huge spreadsheet, and I’m concerned about performance issues. Nonetheless this is supposed to work according to the documentation, unless I’m missing something.
Can you please point out what I’m doing wrong here?
Thanks a lot, I appreciate your help.