Selective Match/Vlookup (?) on Various Criteria in Single Row

[m3ko]

Hello everyone,
If you can, I am hoping for some help here.
I tried looking for solutions already but haven't found what I need yet - will still look anyway.
Sort of relevant but not quite:
(Vlookup - Find partial match from the lookup_value)

Anyway, my current roadblock:
I have exported some data from our system. It contains a list of addresses for each of our customers.
However, the data is a bit scattered, with the address being separated into a few columns and not all in the same format.
I'm hoping to add a new column with the Province Code for each of the address.
The problem is that, as mentioned, the address date isn't all uniform. Some have the full address with the province names, some just have the abbreviated province names, and, others just the city.
I tried to create a LookUp table containing the different variations of city names, province name shorthand and the correct corresponding Province Code.
But I am unable to get it to just "match" up and populate the right Province Code.
In my LookUp table, I am hoping to have all the city variations in the same row as the Prov. code I want to use.
Hope this makes sense and thank you so much in advance for all the help.
Let me know if there are already solutions to this that I haven't found yet.

Thank you.

Sales Report 2019 to 2024-10 with Province.State Field.xlsx
	V	W	X	Y	Z	AA	AB
3	Combined Address					City Variations	Prov.
4	MALLAIG, ALBERTA,T0A 2K0,			#N/A		ALBERTA, AB, A.B.	AB
5	ABBOTSFORD, BC V2T 0C4, , ,			#N/A		BRITISH COLUMBIA, BC, B.C.	BC
6	RED DEER COUNTRY, AB,T4E 0C6,					MANITOBA, MB, M.B.	MB
7	LANGLEY, BC V1M 0A9, ,					NEW BRUNSWICK, NB, N.B.	NB
8	ST-BRUNO, QC J3V 6B9, , ,					NEWFOUNDLAND, NL, N.L.	NL
9	REGINA, SK,S4N 5P7, ,					NORTHWEST TERRITORIES, NT, N.T.	NT
10	ABBOTSFORD, BC,V2T 6J8, ,					NOVA SCOTIA, NS, N.S.	NS
11	GARIBALDI HIGHLANDS, B.C.,V0N 1T0, ,					NUNAVUT, NU, N.U.	NU
12	CHIBOUGAMAU, QUEBEC,G8P 2Z8, ,					ONTARIO, ON, O.N.	ON
13	NOTRE-DAME-DES-MONTS, QUEBEC, ,G0T 1L0					PRINCE EDWARD ISLAND, PE, P.E.	PE
14	BOX 517,SLAVE LAKE, AB,T0G 2A0					QUEBEC, QC, Q.C., PQ	QC
15	JOLIETTE, QC, ,J6E 2S4					SASKATCHEWAN, SK, S.K.	SK
16	MERRITT, B.C.,V1K 1C2, ,					YUKON, YT, Y.T.	YT
Customer Province XRef-unlinked

Cell Formulas
Range	Formula
Y4	Y4	=INDEX(Table5[[#All],[Prov.]], MATCH(V4, Table5[[#All],[City Variations]], 0))
Y5	Y5	=INDEX(Table5[[#All],[Prov.]], MATCH(V22, Table5[[#All],[City Variations]], 0))
V4:V16	V4	=TEXTJOIN(",",TRUE,SALESDATACANADA89[@[SoldToAddr2]:[SoldToAddr4]])

EDIT:
Sorry, I should add that I already combined all the scattered address data into one line - used the "TEXTJOIN" formula. Not sure if this will affect anything of if I should avoid using this.

Thank you

 

[zero269]

I'm hoping to add a new column with the Province Code for each of the address.
The problem is that, as mentioned, the address date isn't all uniform. Some have the full address with the province names, some just have the abbreviated province names, and, others just the city.
I tried to create a LookUp table containing the different variations of city names, province name shorthand and the correct corresponding Province Code.
But I am unable to get it to just "match" up and populate the right Province Code.
In my LookUp table, I am hoping to have all the city variations in the same row as the Prov. code I want to use.

Assuming your Province names in the source data are in a single column, you can add a helper column to clean and replace the old value with a new value.

I tried a sample test using XLOOKUP to see if this will help...

I'm using the LET function to both TRIM the Lookup Value, and the Lookup Array just incase there are any spaces before or after your source data. I added some in the sample data to confirm it will work. However, that doesn't mean that your source data may not have other discrepencies. However, using the helper column will help you find them so they can be corrected.

The LEFT Table is the Source Data containing your current Province name (old) and the Helper column (New) where you use the formula to both cleanup the OLD value and return the new value from the Replace column in the RIGHT Table... which is the Table you need to create to list your Find & Replace values.

The RIGHT Table should not have any discrepencies. It should have exactly what you want to FIND and what to REPLACE with. To make sure, just use an empty column to the right of the table to make sure; then Copy/Paste Values only over the FIND column. Just make sure your Tables and Column Headers are named how you need them.

=TRIM(t_Find_Replace[@Find])

VBA Testing.xlsm
	A	B	C	D	E
1	Province OLD	Province NEW		Find	Replace
2	ALBERTA	AB		ALBERTA	AB
3	AB	AB		AB	AB
4	A.B.	AB		A.B.	AB
5	BRITISH COLUMBIA	BC		BRITISH COLUMBIA	BC
6	BC	BC		BC	BC
7	B.C.	BC		B.C.	BC
REPLACE

Cell Formulas
Range	Formula
B2:B7	B2	=LET( Old, TRIM([@[Province OLD]]), Find, TRIM(t_Find_Replace[Find]), TRIM(XLOOKUP(Old,Find,t_Find_Replace[Replace])))