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.
Your help would be greatly appreciated.
Biz
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