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 🙂
>Solution :
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)