How to Xlookup fuzzy match and standard Xlookup in the same formula?

[Lacan]

Hello Guys,

Would like to Xlookup fuzzy match and standard Xlookup in the same formula.
Through my daily agenda sometimes have to write a different hour before name of customer as you can see in table below cell D4.
Even though would like to match the sales goal of customer.
Can you please help with formula to both sides?
Thank you very much Guys.

Fórmula Principal para o Ficheiro VDF.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3			HORA	CUSTOMER	SALES GOAL				CUSTOMER	SALES GOAL
4			09:00	09H30 JOHN				DELINA	JOHN	3000
5			10:00	HANNA	250			ELISA	RICKY	4000
6			11:00	ELISA	900			HANNA	SARAH	6500
7			12:00	PETER	4500			JACK	HANNA	250
8			13:00					JOHN	ELISA	900
9			14:00					MARIA	DELINA	1000
10			15:00					PETER	PETER	4500
11			16:00					RICKY	JACK	7000
12			17:00					SARAH	MARIA	6020
13			18:00
14			19:00
15
16
Folha13

Cell Formulas
Range	Formula
H4:H14	H4	=SORT(Tabela8[CUSTOMER])
E4:E14	E4	=IFERROR(XLOOKUP(D4,Tabela8[CUSTOMER],Tabela8[SALES GOAL],0,0),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
D4:D14	List	=$H$4#

 

[Lacan]

If you put some identifying character before the extra text (eg I have used a colon) then you could use something like this
You would have to adjust the table name in the formula.

Lacan.xlsm
	C	D	E	F			I	J
3	HORA	CUSTOMER	SALES GOAL				CUSTOMER	SALES GOAL
4	0.375	JACK DE WALL : TEST IT	7000				JOHN	3000
5	0.416666667	HANNA	250				RICKY	4000
6	0.458333333	ELISA	900				SARAH	6500
7	0.5	10H00 PETER	4500				HANNA	250
8	0.541666667						ELISA	900
9	0.583333333						DELINA	1000
10	0.625						PETER	4500
11	0.666666667						JACK DE WALL	7000
12	0.708333333						MARIA	6020
13	0.75
14	0.791666667
Sheet2

Cell Formulas
Range	Formula
E4:E14	E4	=XLOOKUP(REGEXREPLACE(TRIM(TEXTBEFORE(D4&":",":")),"\d.+? ","",,1),Table1[CUSTOMER],Table1[SALES GOAL],"")

Brilliant @Peter_SSs
More than enough!
Thank you very much.

 

[Peter_SSs]

You're welcome.