Wildcards Lookup

[Biz]

Hi,

I'm trying to lookup codes and return Desc_Type using vlookups with wildcards.
In Col B I have created formula which works compared to column C.

I was wondering if there a better way to approach this problem.

Excel Formula to Categorise Items.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1													Code	Desc_Type
2	Code	Formula	Desc_Type	Match									1	Construction
3	1	Construction	Construction	TRUE									A0101	Fees
4	A0101	Fees	Fees	TRUE									A201	Construction
5	A201	Construction	Construction	TRUE									A301	Construction Contingency
6	A301	Construction Contingency	Construction Contingency	TRUE									C	Construction
7	C0101	Construction	Construction	TRUE									C201	Construction Contingency
8	C0199	Construction	Construction	TRUE									D	Fees
9	C101	Construction	Construction	TRUE									E	FFE
10	C101	Construction	Construction	TRUE									H	Fees
11	C102	Construction	Construction	TRUE									K	Fees
12	C201	Construction Contingency	Construction Contingency	TRUE									P0101	Programme Contingency
13	C301	Construction	Construction	TRUE									R0101	Construction
14	C401	Construction	Construction	TRUE									T	Fees
15	C501	Construction	Construction	TRUE									Y101	Programme Contingency
16	E101	FFE	FFE	TRUE									Y201	Escalation
17	E102	FFE	FFE	TRUE									Z	Fees
18	D0101	Fees	Fees	TRUE
19	D101	Fees	Fees	TRUE
20	D103	Fees	Fees	TRUE
21	H0101	Fees	Fees	TRUE
22	K101	Fees	Fees	TRUE
23	P0101	Programme Contingency	Programme Contingency	TRUE
24	R0101	Construction	Construction	TRUE
25	R0101	Construction	Construction	TRUE
26	T101	Fees	Fees	TRUE
27	Y101	Programme Contingency	Programme Contingency	TRUE
28	Y102	Programme Contingency	Programme Contingency	TRUE
29	Y102	Programme Contingency	Programme Contingency	TRUE
30	Y103	Programme Contingency	Programme Contingency	TRUE
31	Y201	Escalation	Escalation	TRUE
32	Y202	Escalation	Escalation	TRUE
33	Y203	Escalation	Escalation	TRUE
34	Y204	Escalation	Escalation	TRUE
35	Z0101	Fees	Fees	TRUE
Trial 4

Cell Formulas
Range	Formula
B3:B35	B3	=IF(LEN(A3)=1,VLOOKUP(A3+0,$M$2:$N$17,2,0),IF(NOT(ISERROR(VLOOKUP(LEFT(A3,2)&"*",$M$2:$N$17,2,0))),VLOOKUP(LEFT(A3,2)&"*",$M$2:$N$17,2,0),VLOOKUP(LEFT(A3,1)&"*",$M$2:$N$17,2,0)))
D3:D35	D3	=B3=C3

Your help would be greatly appreciated.

Biz

 

[Fluff]

You can simplify that formula like

=IF(LEN(A3)=1,VLOOKUP(A3+0,$M$2:$N$17,2,0),IFERROR(VLOOKUP(LEFT(A3,2)&"*",$M$2:$N$17,2,0),VLOOKUP(LEFT(A3,1)&"*",$M$2:$N$17,2,0)))

 

[Biz]

Thank you very much Fluff

 

[Fluff]

You're welcome & thanks for the feedback.