英文:
VLOOKUP using `*` in the Lookup
问题
我在数据库中有以下示例数据集：
| 名称 | 值 |
|---|---|
| 汽车 | 0 |
| *汽车 | 10.85 |
| 公交车 | 0 |
| *公交车 | 21.20 |
| 面包车 | 0 |
| 卡车 | 9.90 |
我正在使用以下VLOOKUP函数根据“名称”来获取“值”。
=VLOOKUP(A1,'LookupSheet'!$A$1:$B$7,2,FALSE)
我希望我的结果看起来像这样（基于VLOOKUP）：
| 名称 | 值 |
|---|---|
| 汽车 | 0 |
| *汽车 | 10.85 |
| 公交车 | 0 |
| *公交车 | 21.20 |
| 面包车 | 0 |
| 卡车 | 9.90 |
但我最终得到了以下结果（汽车和公交车的值没有*，而不应该有*）：
| 名称 | 值 |
|---|---|
| 汽车 | 10.85 |
| *汽车 | 10.85 |
| 公交车 | 21.20 |
| *公交车 | 21.20 |
| 面包车 | 0 |
| 卡车 | 9.90 |
我做错了什么？有没有办法修改VLOOKUP代码，以便只获取带有的名称的值，当存在两个相同名称的实例时（一个带，一个不带*）？
抱歉，我是Excel的新手，正在努力学习！任何帮助将不胜感激 
英文:
I have the following set of example data in a database:
| Name | Values |
|---|---|
| Car | 0 |
| *Car | 10.85 |
| Bus | 0 |
| *Bus | 21.20 |
| Van | 0 |
| Truck | 9.90 |
I am using the following Vlookup to gather "values" based on "name"
=VLOOKUP(A1,'LookupSheet'!$A$1:$B$7,2,FALSE)
I want my results to look like this (based on the vlookup):
| Name | Values |
|---|---|
| Car | 0 |
| *Car | 10.85 |
| Bus | 0 |
| *Bus | 21.20 |
| Van | 0 |
| Truck | 9.90 |
But what I end up getting is the following (values for Car and Bus without the * where there should be none):
| Name | Values |
|---|---|
| Car | 10.85 |
| *Car | 10.85 |
| Bus | 21.20 |
| *Bus | 21.20 |
| Van | 0 |
| Truck | 9.90 |
What am I doing wrong? Is there anyway I can modify the Vlookup code such that I only get values for the names with a * where there are 2 instances of the same name (one with a * and one without)?
Sorry, I am new to Excel and trying to learn!
Any help would be much appreciated
答案1
得分: 5
你需要转义 *，否则它会在 VLOOKUP 中充当通配符。
尝试：
=VLOOKUP(SUBSTITUTE(A1,"*","~*"),'LookupSheet'!$A$1:$B$7,2,FALSE)
英文:
You need to escape the * which otherwise acts as a wildcard in the VLOOKUP.
try:
=VLOOKUP(SUBSTITUTE(A1,"*","~*"),'LookupSheet'!$A$1:$B$7,2,FALSE)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论