Asterisk in VLOOKUP search
 

Hi,

I'm using a column of data in a VLOOKUP search. Unfortunately the actual
data can have asterisks at the end, e.g.

MyDataColumn
EBV-1003
EBV-1004*
EBV-1005

MyDataTable
EBV-1003 Valve
EBV-1003* Test Valve
EBV-1004 Valve
EBV-1004* Test Valve
EBV-1005 Valve
EBV-1005* Test Valve

ValveType = VLOOKUP(MyDataColumn, MyDataTable, 2, FALSE)

....as you can see EBV-1004* will not find the test valve, it will find the
first match because the asterisk is treated as a wildcard. I have solved the
problem using SEARCH and REPLACE to repace the "*" with "~*" as per Help. It
works fine but man oh man, the formula is complex. (Because of all the
necessary ISERRORs.)

My question is: can I tell VLOOKUP to just look it up literally (binary
search) and not use wildcards? Is there a flag to set or maybe a different
function to use? Did I miss one of the lookup functions somewhere?

TIA,
Charlie

Asterisk in VLOOKUP search
 

Lookup:
SUBSTITUTE(A1,"*","")

instead
--
Gary''s Student - gsnu200781


"Charlie" wrote:

Hi,

I'm using a column of data in a VLOOKUP search. Unfortunately the actual
data can have asterisks at the end, e.g.

MyDataColumn
EBV-1003
EBV-1004*
EBV-1005

MyDataTable
EBV-1003 Valve
EBV-1003* Test Valve
EBV-1004 Valve
EBV-1004* Test Valve
EBV-1005 Valve
EBV-1005* Test Valve

ValveType = VLOOKUP(MyDataColumn, MyDataTable, 2, FALSE)

...as you can see EBV-1004* will not find the test valve, it will find the
first match because the asterisk is treated as a wildcard. I have solved the
problem using SEARCH and REPLACE to repace the "*" with "~*" as per Help. It
works fine but man oh man, the formula is complex. (Because of all the
necessary ISERRORs.)

My question is: can I tell VLOOKUP to just look it up literally (binary
search) and not use wildcards? Is there a flag to set or maybe a different
function to use? Did I miss one of the lookup functions somewhere?

TIA,
Charlie

Asterisk in VLOOKUP search
 

Yes, thanks, that is much simpler. I modified it to place a tilde in front
of the asterisk because I actually need to find that asterisk.

SUBSTITUTE(A1,"*","~*")

(I see the SUBSTITUTE function is binary search, not using wildcards.)


"Gary''s Student" wrote:

Lookup:
SUBSTITUTE(A1,"*","")

instead
--
Gary''s Student - gsnu200781


"Charlie" wrote:

Hi,

I'm using a column of data in a VLOOKUP search. Unfortunately the actual
data can have asterisks at the end, e.g.

MyDataColumn
EBV-1003
EBV-1004*
EBV-1005

MyDataTable
EBV-1003 Valve
EBV-1003* Test Valve
EBV-1004 Valve
EBV-1004* Test Valve
EBV-1005 Valve
EBV-1005* Test Valve

ValveType = VLOOKUP(MyDataColumn, MyDataTable, 2, FALSE)

...as you can see EBV-1004* will not find the test valve, it will find the
first match because the asterisk is treated as a wildcard. I have solved the
problem using SEARCH and REPLACE to repace the "*" with "~*" as per Help. It
works fine but man oh man, the formula is complex. (Because of all the
necessary ISERRORs.)

My question is: can I tell VLOOKUP to just look it up literally (binary
search) and not use wildcards? Is there a flag to set or maybe a different
function to use? Did I miss one of the lookup functions somewhere?

TIA,
Charlie