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

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
>

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
> >