个人中心
gpt4 book ai didi

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

转载 作者：bug小助手 更新时间：2023-10-25 18:26:13 26 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)

26 4 0
文章推荐： How to debounce search suggestions in flutter's SearchPage Widget?(如何在Fighter的SearchPage Widget中去掉搜索建议？)
文章推荐： Zeromq 1 to 1 async req-rep unreliable pattern(Zeromq 1到1的重复请求不可靠模式)
文章推荐： Unhandled Promise Rejection: Error: Missing parameter. You must specify placeId(未处理的承诺拒绝：错误：缺少参数。必须指定PlaceID)
文章推荐： Android push notification icon - how to change background color(Android推送通知图标-如何更改背景颜色)
bug小助手
个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章
滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com