I have some names from g6 to g9 on "Sheet 1" of my excel:

在我的表格1上，我有一些从G6到G9的名字：

• Z Purton


• H Bowman


• C L Chau(-2)


• M F Poon(-7)

I have these names in column B of another sheet called "Names" but in a different order, and also without the "(-2)":

我把这些名字放在另一张纸的B栏，名字叫“名字”，但顺序不同，而且也没有“(-2)”：

• M F Poon


• H Bowman


• Z Purton


• C L Chau

In column L of the same sheet, I have a bunch of numbers which are the points corresponding to the names.

在同一张表的L栏中，我有一串数字，它们是名称对应的点。

Back to sheet1 of my excel, i did

回到我的成绩表1，我做到了

=VLOOKUP(I6, Names!$B$5:$L$55, 11, FALSE) 

This worked for the first two names, printing their scores, 0.75 and 0.5, but then it said #N/A for the last two names as they had a number next to them, making them not match the names from Sheet 1.

这对前两个名字有效，打印了他们的分数0.75和0.5，但随后它为最后两个名字显示了#N/A，因为他们旁边有一个数字，使他们与表1中的名字不匹配。

I then tried to do

然后我试着去做

=VLOOKUP(I6, TrainerRanking!$B$5:$L$55, 11, TRUE) 

Now, there were no more #N/A's. However, for the first name - Z Purton, it should have printed 0.75 but it printed 0

现在，没有更多的#N/A。然而，对于名字-Z Purton，它应该打印0.75，但它打印0

is there a way to fix this?

有什么办法可以解决这个问题吗？

Try using TEXTBEFORE( )

尝试使用TEXTBEFORE()

=VLOOKUP(TEXTBEFORE(I6,"(",,,1,I6), Names!$B$5:$L$55, 11, FALSE)

Or,

或,

=XLOOKUP(TEXTBEFORE(I6:I9,"(",,,1,I6:I9), Names!B5:B55, Names!L5:L55, "")

The TEXTBEFORE( ) function has 2 mandatory parameters and 4 optional parameters, the parameters which shows within square brackets are called optional.

TEXTBEFORE()函数有2个必选参数和4个可选参数，方括号中显示的参数称为可选参数。

So that said, the TEXTBEFORE( ) Function syntax is

也就是说，TEXTBEFORE()函数的语法是

=TEXTBEFORE(text,delimiter,[instance_num],[match_mode],[match_end],[if_not_found]) 

therefore in the above formula I have used the function as

因此，在上面的公式中，我使用了如下函数

=TEXTBEFORE(I6:I9,"(",,,1,I6:I9) 

hope you can relate it now. Where I6:I9 is my text, ( is my delimiter, instance_num and match_mode is nothing here taken, while the match_end is 1 & if not found is same I6:I9.

希望你现在能把它联系起来。其中，I6：I9是我的文本，(是我的分隔符，INSTANCE_NUM和MATCH_MODE在这里什么都不接受，而Match_End是1&如果没有找到，则相同I6：I9。

You can also read the MS Documentation here

您还可以在此处阅读MS文档

Alternative approach for all users, as mentioned in comments by P.b Sir.

所有用户的替代方法，如P.B先生的评论中所述。

=VLOOKUP(IFERROR(LEFT(I6,FIND("(",I6)-1),I6),Names!$B$5:$L$55,11,0)

Here is another way:

这里有另一种方法：

INDEX($B$10:$B$13,MATCH(IFERROR(LEFT(A3,FIND("(",A3,1)-1),A3),$A$10:$A$13,0))

Adding trim() may be useful as the "(-2) etc may pick up a space prior, so:

添加trim()可能会很有用，因为“(-2)等可能会优先选择一个空格，因此：

INDEX($B$10:$B$13,MATCH(TRIM(IFERROR(LEFT(A3,FIND("(",A3,1)-1)),A3),$A$10:$A$13,0))

Also, this will work in earlier versions of Excel prior to those useful functions like textbefore() and textafter()...

此外，这将在早期版本的Excel中运行，在这些有用的函数(如TextBever()和Texttafter()之前)...