使用 Excel vlookups 进行精确大小写匹配
我有一个包含“唯一”ID 的电子表格。问题是它们仅区分大小写,这意味着我有:a06D000000QO5uW & a06D000000QO5uw。
我想对这些 ID 执行 vlookup 并拉回相关值。可以使用我发现的这篇文章进行区分大小写的匹配:
http://support.microsoft.com/kb/ 214264
我发现的问题是,由于 vlookup 嵌套在 Exact 函数中,它返回它找到的第一个匹配项,这可能不是我要查找的匹配项。如果我使用本文中的数据示例,它看起来像:
Name Age Joe
Mary 32
joe 48
Bob 53
Sue 27
Joe 30
并且查找看起来像:
=IF(EXACT(C1,VLOOKUP(C1,A1:B6,1,FALSE))=TRUE,VLOOKUP(C1,A1:B6,2,FALSE),"No exact match")
问题似乎是测试“Joe”的 vlookup 首先遇到“joe”,因为它不区分大小写将其作为匹配项返回 - 这意味着它永远不会到达“Joe”。然后,确切的函数会失败,因为它正在尝试针对“joe”测试“Joe”。
有什么办法解决这个问题吗?我真的想避免走 VBA 路线,因为我们有 Mac 和 Window 用户,所以我想保留公式。
I have a spreadsheet which contains 'unique' IDs. The problem is that they are only case sensitive unique, meaning that I have: a06D000000QO5uW & a06D000000QO5uw.
I want to perform a vlookup on these IDs and pull back a related value. It's possible to do case sensitive matching using this article I found:
http://support.microsoft.com/kb/214264
The problem I have found is that because the vlookup is nested within the Exact function, it returns the first match it finds, which may not be the one I am after. If I use the data sample from the article, it looks like:
Name Age Joe
Mary 32
joe 48
Bob 53
Sue 27
Joe 30
and the look up looks like:
=IF(EXACT(C1,VLOOKUP(C1,A1:B6,1,FALSE))=TRUE,VLOOKUP(C1,A1:B6,2,FALSE),"No exact match")
The problem seems to be that the vlookup to test "Joe" comes across "joe" first of all and because it isn't case sensitive returns that as a match - meaning it never gets to "Joe". The exact function then fails because it is trying to test "Joe" against "joe".
Is there any way around this? I really wanted to avoid going down the VBA route because we have a mix of Mac and Window users and so I wanted to keep it to formulas.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您的问题(使用这个确切的示例)已涵盖 此处
) 一起将针对 joe 返回 30(如果未找到匹配则不适用)。
=INDEX(B1:B6,MATCH(1,--EXACT(A1:A6,C1),0))
=LOOKUP(1, 1/EXACT(A1:A6,C1),B1:B6)
Your problem (with this exact example) is covered here
) together will return 30 against joe (NA if no match is found).
=INDEX(B1:B6,MATCH(1,--EXACT(A1:A6,C1),0))
=LOOKUP(1,1/EXACT(A1:A6,C1),B1:B6)
我认为有一个稍微好一点的方法,因为它不使用数组公式。
I think that there is a slightly better way as it does not use an array formula.