Fuzzy Match with Wildcards

[jzellman]

Trying to check some accounting records. Accounting transactions are posted to certain indirect pools based on a unique combination or Dept and Account Number (Mapping Table below). .Im trying to add the pool name to the Accounting Transactions. In the mapping table there are wildcard fields (can be single ("?") or multiple("*") characters. There are 14,000 entries in the Mapping Table and over 20,000 accounting transactions.

Any ideas on doing this. I tried fuzzy match, etc and nothing seems to work...thanks

Mapping Table				Accounting Transactions
Department	Account #	Indirect Pool Name		Department	Account #	Amount	Add Pool Name
100.100.US.ABCD.00	98.051.000	G&A		100.100.US.ABCD.00	98.051.000	$ 1,022
100.100.US.ABCD.01	98.051.000	G&A		100.100.US.ABCD.01	98.051.000	$ 49
100.100.US.ABCD.02	*	G&A		100.100.US.ABCD.02	98.051.101	$ 5,433
*.100.?R.*	*.000	G&A		101.100.US.DEFG.01	98.051.56A	$ 544
*.100.?R.*	98.05*	G&A		102.100.US.GFRR.AA	98.055.000	$ 56,454
101.100.US.DEFG.00	98.051.000	Overhead
101.100.US.DEFG.01	??.051.??A	Overhead
102.100.*	98.051.000	Overhead
103.100.GT.FDDF.??	98.055.000	Fringe
103.100.DD.*	*.056.000	Fringe

 

[AlanY]

this works with your sample data

Book1
	A	B	C	D	E	F	G	H
1	Mapping Table				Accounting Transactions
2	Department	Account #	Indirect Pool Name		Department	Account #	Amount	Add Pool Name
3	100.100.US.ABCD.00	98.051.000	G&A		100.100.US.ABCD.00	98.051.000	$ 1,022	G&A
4	100.100.US.ABCD.01	98.051.000	G&A		100.100.US.ABCD.01	98.051.000	$ 49	G&A
5	100.100.US.ABCD.02	*	G&A		100.100.US.ABCD.02	98.051.101	$ 5,433	G&A
6	*.100.?R.*	*.000	G&A		101.100.US.DEFG.01	98.051.56A	$ 544	Overhead
7	*.100.?R.*	98.05*	G&A		102.100.US.GFRR.AA	98.055.000	$ 56,454	Fringe
8	101.100.US.DEFG.00	98.051.000	Overhead
9	101.100.US.DEFG.01	??.051.??A	Overhead
10	102.100.*	98.051.000	Overhead
11	103.100.GT.FDDF.??	98.055.000	Fringe
12	103.100.DD.*	*.056.000	Fringe
Sheet5

Cell Formulas
Range	Formula
H3:H7	H3	=IFERROR(INDEX($C$3:$C$12,MATCH(E3,$A$3:$A$12,0)),INDEX($C$3:$C$12,MATCH(F3,$B$3:$B$12,0)))

 

[jzellman]

Thanks. Your Fringe answer does not work. Both columns need to match. Fringe pools have a Dept # starting with "103"