Search code examples
exceldatabaseexcel-formulavlookup

VLOOKUP using `*` in the Lookup

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)