gpt4 book ai didi

Excel VLOOKUP true or false issue(Excel VLOOKUP真假问题)

转载 作者:bug小助手 更新时间:2023-10-25 18:26:13 27 4
gpt4 key购买 nike



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

我有一些名字从g6到g9在“表1”我的Excel:



  • 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?

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


更多回答

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

使用TEXTBEFORE()函数-->=VLOOKUP(TEXTBEFORE(I6,“(”,,,1,I6),NAMES!$B$5:$L$55,11,FALSE)

Or use XLOOKUP() and TEXTBEFORE() --> =XLOOKUP(TEXTBEFORE(I6:I9,"(",,,1,I6:I9), Names!B5:B55, Names!L5:L55, "")

或使用XLOOKUP()和TEXTBEFORE()-->=XLOOKUP(TEXTBEFORE(I6:I9,“(”,,,1,I6:I9),NAMES!B5:B55,NAMES!L5:L55,“”)

优秀答案推荐

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:

这里是另一种方法:


enter image description here


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()之前)...


更多回答

Thanks, just wondering, what does ",,,1" do? I'm a bit confused on why there are three commas next to each other

谢谢,只是想知道,“,,,1”是做什么的?我有点搞不懂为什么有三个逗号挨着

@NNBananas yeah sure let me tell you, the 1 means [match_end] which is an optional parameter.

@NNBananas是的,让我告诉你,1的意思是[Match_End],这是一个可选参数。

@NNBananas the TEXTBEFORE() function has 2 mandatory parameters and 4 optional parameters, the parameters which shows within square brackets are called optional. So that said, the TEXTBEFORE() Function syntax is =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

@NNBananas TEXTBEFORE()函数有2个必选参数和4个可选参数,方括号中显示的参数称为可选参数。也就是说,TEXTBEFORE()函数的语法是=TEXTBEFORE(TEXT,DELIMITER,[INSTANCE_NUM],[MATCH_MODE],[MATCH_END],[IF_NOT_FOUND])。因此,在上面的公式中,我使用了函数=TEXTBEFORE(I6:I9,“(”,,,1,I6:I9)),希望您现在可以将其联系起来。其中,I6:I9是我的文本,(是我的分隔符,INSTANCE_NUM和MATCH_MODE在这里是空的,而Match_End是1,如果未找到,则相同I6:I9

Oh, I understand now. thanks!

哦,我现在明白了。谢谢!

For older Excel versions one could use =VLOOKUP(IFERROR(LEFT(I6,FIND("(",I6)-1),I6),Names!$B$5:$L$55,11,0)

对于较旧的Excel版本,可以使用=VLOOKUP(IFERROR(LEFT(I6,Find(“(”,I6)-1),I6),NAMES!$B$5:$L$55,11,0)

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com