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#

 

[Cubist]

Try:

=SUM(IF(ISNUMBER(SEARCH(Tabela8[CUSTOMER],D4)),Tabela8[SALES GOAL],0))

 

[Peter_SSs]

Try:

=SUM(IF(ISNUMBER(SEARCH(Tabela8[CUSTOMER],D4)),Tabela8[SALES GOAL],0))

That would fail if a shorter name in Tabela8 occurs within a longer customer name in col D, as in my sample data.


@Lacan
If all the names really are single words like your sample then try this. No need to copy down.

25 01 20.xlsm
	D	E	F	G	H	I	J
1
2
3	CUSTOMER	SALES GOAL				CUSTOMER	SALES GOAL
4	09H30 JOHN	3000				JOHN	3000
5	HANNA	250				RICKY	4000
6	ANNELISA					SARAH	6500
7	PETER	4500				HANNA	250
8						ELISA	900
9						DELINA	1000
10	ELISA	900				PETER	4500
11						JACK	7000
12						MARIA	6020
13
14
15
16
XLOOKUP

Cell Formulas
Range	Formula
E4:E15	E4	=XLOOKUP(TEXTAFTER(D4:D15," ",,,,D4:D15),Tabela8[CUSTOMER],Tabela8[SALES GOAL],"")
Dynamic array formulas.

If the names could be more than one word then perhaps this (but fix the table name). Again, no need to copy down.

25 01 20.xlsm
	D	E	F	G	H	I	J
1
2
3	CUSTOMER	SALES GOAL				CUSTOMER	SALES GOAL
4	09H30 JOHN	3000				JOHN	3000
5	HANNA	250				RICKY	4000
6	ELISA HALL	900				SARAH	6500
7	PETER	4500				HANNA	250
8	ANNELISA HALL					ELISA HALL	900
9	10H15 JACK BLACK	7000				DELINA	1000
10						PETER	4500
11						JACK BLACK	7000
12						MARIA	6020
13
14
15
16
XLOOKUP (2)

Cell Formulas
Range	Formula
E4:E15	E4	=XLOOKUP(IF(ISNUMBER(-LEFT(D4:D15,1)),TEXTAFTER(D4:D15," "),D4:D15),Tabela84[CUSTOMER],Tabela84[SALES GOAL],"")
Dynamic array formulas.

 

[hagia_sofia]

If you have access to the new REGEX functions and the time has the same format maybe you could try something like:

=XLOOKUP(REGEXREPLACE(D4:D14,"\d{2}.\d{2}\s","",,1),Tabela8[CUSTOMER],Tabela8[SALES GOAL])

 

[Peter_SSs]

If you have access to the new REGEX functions and the time has the same format maybe you could try something like:

=XLOOKUP(REGEXREPLACE(D4:D14,"\d{2}.\d{2}\s","",,1),Tabela8[CUSTOMER],Tabela8[SALES GOAL])

Good thought, though I'm not sure how widely available the function is yet. Two comments:

• It probably needs the "if not found" option in XLOOKUP to deal with the blank rows in the OP's data
• Given the sample data the pattern could probably be reduced?

=XLOOKUP(REGEXREPLACE(D4:D14,".*\d ","",,1),Tabela8[CUSTOMER],Tabela8[SALES GOAL],"")

.. or if it is possible there are no minutes in the time and so there might be an entry like "11H JOHN" then perhaps

=XLOOKUP(REGEXREPLACE(D4:D14,"\d.+? ","",,1),Tabela8[CUSTOMER],Tabela8[SALES GOAL],"")

 

[hagia_sofia]

Many thanks @Peter_SSs for the adjustments - I am still trying learning REGEX so there is a high chance that there will be a more effective pattern.

 

[Lacan]

My Dears @Cubist @Peter_SSs and @hagia_sofia

Thank you very much Guys!!!

=XLOOKUP(REGEXREPLACE(D4:D14,"\d.+? ","",,1),Tabela8[CUSTOMER],Tabela8[SALES GOAL],"")

works very Beatiful because have also no single names and because it matches your advance entry thoughts the formula fits very well.
So far so good.
Lets supposse that could have also text after Name. Is formula would be long?
Very Grateful!!!

Fórmula Principal para o Ficheiro VDF.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3			HORA	CUSTOMER	SALES GOAL				CUSTOMER	SALES GOAL
4			09:00	JACK DE WALL TEST IT				DELINA	JOHN	3000
5			10:00	HANNA	250			ELISA	RICKY	4000
6			11:00	ELISA	900			HANNA	SARAH	6500
7			12:00	10H00 PETER	4500			JACK DE WALL	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 DE WALL	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	=XLOOKUP(REGEXREPLACE(D4,"\d.+? ","",,1),Tabela8[CUSTOMER],Tabela8[SALES GOAL],"")
Dynamic array formulas.

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

 

[Peter_SSs]

works very Beatiful

Good news. Thanks for the confirmation.

Lets supposse that could have also text after Name. Is formula would be long?

Formula would not be reliable. There is no way to determine what is the last text in a name and what is the first text after the name.

 

[Lacan]

Good news. Thanks for the confirmation.


Formula would not be reliable. There is no way to determine what is the last text in a name and what is the first text after the name.

Dear @Peter_SSs

Thank you very much for clarifying!
All the best Guys!

 

[Peter_SSs]

Thank you very much for clarifying!

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],"")