X/Vlookup or Index Match with Wildcards in Array

[BHG 463]

The Account Name I am trying to look up is static. I am trying to find a match on two criteria where one of arrays has Wildcards that are built into the Name Variation column. The country needs to be exact match.
If I have the customer name "Bob's Ace Hardware" in the United States and my array data has *Ace Hardware* and United States, I want to be able to return the Common Name of Ace Hardware and that it was found on the Sort Order row.
Office 365 on Windows.

Mr Excel Help.xlsx
	A	B	C	D	E	F	G	H	I
1						Wildcard Match	Exact Match
2	Common Name	Name Variation	Country	Sort Order		Account Name	Country	Common Name (Returned Value)	Sort Order (Returned Value)
3	Ace Hardware	*ACE HARDWARE*	Indonesia	1		Bob's Ace Hardware	United States	Ace Hardware	1
4	Ace Hardware	*KAWAN LAMA SEJAHTERA*	Indonesia	2		Bob's AceHardware	United States	Ace Hardware	7
5	Ace Hardware	ACE HDW*	United States	3		Aces Hardware	United States	#N/A	#N/A
6	Ace Hardware	* ACE HARDWARE*	United States	4		Ace Hardware	Canada	#N/A	#N/A
7	Ace Hardware	ACE*HARDWARE	United States	5		Sysco Food Services	United States	Sysco	10
8	Ace Hardware	ROCK*ACE HARDWARE*	United States	6		Sysco Food Services	France	SYSCO FRANCE	14
9	Ace Hardware	ROCKY?ÇÖS ACE HARDWARE - CORPORATE	United States	7		SyscoFoodServices	France	#N/A	#N/A
10	Ace Hardware	ACE HARDWARE*	United States	8
11	Sygma	SYGMA*	United States	9
12	Sysco	SYSCO *	United States	10
13	SYSCO FRANCE	BRAKE *	France	11
14	SYSCO FRANCE	*DAVIGEL*	France	12
15	SYSCO FRANCE	DAVIGEL*	France	13
16	SYSCO FRANCE	SYSCO *	France	14
17	SYSCO FRANCE	*CHASSIS BRAKES*	France	15
18	TRUE VALUE CO	*TRUE VALUE*	United States	16
19	TRUE VALUE CO	TRUSERV*	United States	17
Sheet1

 

[awoohaw]

Which column has your initial search string(s)?


would you be kind enough to include all the additional info for the xl2bb mini worksheet:
please check all the boxes.

 

[BHG 463]

Which column has your initial search string(s)?


would you be kind enough to include all the additional info for the xl2bb mini worksheet:
please check all the boxes.

View attachment 105751

I hope this is better, I know I am far off. H and I have my search string.

Mr Excel Help.xlsx
	H
11
Sheet1

 

[BHG 463]

I hope this is better, I know I am far off. H and I have my search string.

Mr Excel Help.xlsx
	H
11
Sheet1

Mr Excel Help.xlsx
	A	B	C	D	E	F	G	H	I
1						Wildcard Match	Exact Match
2	Common Name	Name Variation	Country	Sort Order		Account Name	Country	Common Name (Returned Value)	Sort Order (Returned Value)
3	Ace Hardware	*ACE HARDWARE*	Indonesia	1		Bob's Ace Hardware	United States	=LOOKUP(1,IFERROR(MATCH($B$3:$B$19,F3,0),FALSE),$A$3:$A$19)	=LOOKUP(1,IFERROR(MATCH($B$3:$B$19,F3,0),FALSE),$D$3:$D$19)
4	Ace Hardware	*KAWAN LAMA SEJAHTERA*	Indonesia	2		Bob's AceHardware	United States	#N/A	#N/A
5	Ace Hardware	ACE HDW*	United States	3		Aces Hardware	United States	Ace Hardware	5
6	Ace Hardware	* ACE HARDWARE*	United States	4		Ace Hardware	Canada	Ace Hardware	5
7	Ace Hardware	ACE*HARDWARE	United States	5		Sysco Food Services	United States	Sysco	10
8	Ace Hardware	ROCK*ACE HARDWARE*	United States	6		Sysco Food Services	France	Sysco	10
9	Ace Hardware	ROCKY?ÇÖS ACE HARDWARE - CORPORATE	United States	7		SyscoFoodServices	France	#N/A	#N/A
10	Ace Hardware	ACE HARDWARE*	United States	8
11	Sygma	SYGMA*	United States	9
12	Sysco	SYSCO *	United States	10
Sheet1

Cell Formulas
Range	Formula
H4:H9	H4	=LOOKUP(1,IFERROR(MATCH($B$3:$B$19,F4,0),FALSE),$A$3:$A$19)
I4:I9	I4	=LOOKUP(1,IFERROR(MATCH($B$3:$B$19,F4,0),FALSE),$D$3:$D$19)