Return value based on matching text

[Khalil600]

Hello everyone,
I have sheet contains the correct name and number of clients, another sheet contains clients names only but names are not 100% matches with 1st sheet. I need to return client number from 1st sheet and listed in 2nd sheet based on matching names between two sheets.

[#2]

@Khalil600 

If names are not matched that is mix of automatic work (any lookup function) and manual corrections where lookup found nothing.

Alternatively Fuzzy Match in Power Query (or perhaps add-in) could be used, but with it also no warranty everything will be found.

[#3]

Thanks @Sergei Baklan , any way to match two text based on two or more identical words in both texts, for example, I like drinking coffee, and 2nd text I'm drinking coffee every night, the matching here based on two identical words drinking & coffee, and so o

[#4]

@Khalil600 

Not sure. Without predefined words we need to split each text on array of words; exclude from it "a", "the", "and", etc.; exclude characters like "," and "!"; compare lookup array with lookup array of arrays; find row with two or more matches; based on its position return some value.

If possible that's not easy task.

[#5]

Thanks @Sergei Baklan, appreciated your reply.