Comma separated search value matching

[tommsegers]

Hi everyone

I have a Sheet containing a column which contains comma separated values containing locations and a value

Item ID	Location results
112	Location A :1;Location B:3;Location C:4;Location D:1
113	Location A :5;Location F:1;Location X:9;Location Z:1
116	Location AA:3

On a new sheet I have a list of all available locations and need to generate a table that looks like showing the value after the : of that specific location.
And preferable also adds the columns if the ID exists so i don't have to manually create every column header

Location	112	113	116
Location A	1	5	0
Location B	3	0	0
Location AA	0	0	3

Another issue is that the Location names need to be an exact match as some names contain similar words like "Brussels North", "Brussels", "Antwerp North"

I have tried Search formulas without success so far

Thank you
Tom

 

[shinigamilight]

Compatible with older versions of excel

=IFERROR(VALUE(IF(SEARCH($C2,VLOOKUP(D$1,$A$2:$B$4,2,0)),MID(SUBSTITUTE(VLOOKUP(D$1,$A$2:$B$4,2,0),";",REPT(" ",20)),1+FIND(":",SUBSTITUTE(VLOOKUP(D$1,$A$2:$B$4,2,0),";",REPT(" ",20)),SEARCH($C2,SUBSTITUTE(VLOOKUP(D$1,$A$2:$B$4,2,0),";",REPT(" ",20)))),3),"")),"")

Number of chars extracted can be changed by changing the value that's in bold in the formula.