I have a table with clients and specific markers for each shop:
| Client | Markers | Location |
|---|---|---|
| Jane | B,D,K,M,f,n,, | |
| Max | B,D,J,K,M,f,i,n, | |
| Ted | D,i,a,1,J,Y,K,M | |
| Maria | C,D,J,K,M,n | |
| Alex | A,D,K,M,f,i,n | |
| Tom | A,D,K,M,f,m,o,y, | |
| Richard | R,D,J,K,M,f,i,n | |
| X | A,D,K,M,f,n | |
| Red | A,D,K,M,f,i,n, | |
| John | C,D,F,K,M,f,i,n,4 | |
| Lex | T,D,a,1,4,T,K,M | |
| Ted | D,a,1,T,K,M | |
| Jane | D,a,1,T,K,M |
Another table contains the Locations:
| marker | desc |
|---|---|
| A | New York |
| B | Amsterdam |
| C | London |
| H | Tokyo |
| Q | Paris |
| R | Vancouver |
| T | Sydney |
| Y | Auckland |
Now I want to fill first table with locations but going wrong when first marker isn't the location marker. I used: =VLOOKUP([@Markers],TableLocations[marker],1,TRUE), I've tried the MATCH function but this gives the wrong number again.
So only works fine when first character in the marker column matches the marker in the location table.
CodePudding user response：
To find only for first marker location from comma separated values from each cell in first column, you can use-
=XLOOKUP(TEXTBEFORE(B2,","),$G$2:$G$9,$H$2:$H$9)
For multiple location try-
=TEXTJOIN(", ",TRUE,FILTER($H$2:$H$9,ISNUMBER(XMATCH($G$2:$G$9,TOCOL(TEXTSPLIT(B2,",")),0))))
For dynamic spill array at one go, try-
=BYROW(B2:B14,LAMBDA(x,TEXTJOIN(", ",TRUE,FILTER($H$2:$H$9,ISNUMBER(XMATCH($G$2:$G$9,TOCOL(TEXTSPLIT(x,",")),0))))))
CodePudding user response：
Try this, using tables and structured references (which you can change to normal addressing if you preferred, but the former are more dynamic).
In your comments you indicated there would be only one location per client; if you need more than one, please clarify
Edit: corrected missing structured reference
=INDEX(Location[desc],AGGREGATE(14,6,BYCOL(EXACT(TEXTSPLIT([@Markers],","),Location[marker]),LAMBDA(arr,XMATCH(TRUE,arr))),1))
Note: One can get case-sensitive matches using either EXACT or FIND functions. But, because of the null string in the list in your first row (note the doubled-comma in the Markers), FIND will always return a match for that, potentially causing an incorrect result