Lookup value is in a cell with other data separated by commas

[ZZAAKK]

Hi All,

I am having trouble with a LOOKUP formula.

Column A contains various codes separated by commas.
I want a LOOKUP formula in Column B that identifies specific codes in Column A and returns a Name from the table (Column D:E)

I am using Excel 2016, so cannot utilise a lot of the formulas found in Office 365.

Document Codes (Column A)	Branch Name (Column B)
12345, 54896, 78546, BR002	Formula to look up "BR00x" and return Branch Name ... BR002 = London
45895, 58475, BR003, 58956	Formula to look up "BR00x" and return Branch Name ... BR003 = Cardiff
45989, BR001, 55889, 12345	Formula to look up "BR00x" and return Branch Name ... BR001 = Edinburgh
BR002, 58596, 58859, 12348	Formula to look up "BR00x" and return Branch Name ... BR002 = London

Branch Code (Column D)	Branch Name (Column E)
BR001	Edinburgh
BR002	London
BR003	Cardiff

Any help would be greatly appreciated!

 

[vletm]

ZZAAKK​

As You wanted =LOOKUP(MID(A2,FIND("BR00",A2),5),D$2:D$4,E$2:E$4)

 

[Peter Bartholomew]

Sad about the 365. Solution building in 365 bears little relationship to traditional spreadsheet techniques. At the moment

= XLOOKUP(REGEXEXTRACT(codes, "BR\d{3}"), branch, name)

works but the lookup functions are about to be modified to provide better RegEx support.