F1 = a value, text or number, and is the "lookup value" for the for the wild card formula I think I need. (1234, 1cww6, QWE, are examples.)

Want to look at A for the F1 value, and if a match OR a partial match, then return the value in B to column D.

If no match at all, then return nothing to D.

Ex. 1 Partial match

F1 = 23-DD
A2 = 123-DD/48
B2 = $4.00

then D = $4.00

Ex. 2 No match

F1 = 23-DD
A6 = cc_44/9
B6 = $27.00

then D = ""

I was able to do this with an InStr macro, but is way to slow using for each in range.

Data is very long column of part numbers in A with associated price in B.

I intend to use the formula in a macro to do various F1 look ups when the value in F1 (or inputbox) is changed.

It's the ~*??* syntax formula that eludes me.

Thanks,
Howard

Hi Howard,

Am Mon, 19 Jan 2015 00:11:08 -0800 (PST) schrieb L. Howard:

F1 = 23-DD
A2 = 123-DD/48
B2 = $4.00

then D = $4.00

Ex. 2 No match

F1 = 23-DD
A6 = cc_44/9
B6 = $27.00

then D = ""

in D2 try:
=IFERROR(VLOOKUP("*"&$F$1&"*",A2:B2,2,0),"")
or
=IF(SUMPRODUCT(ISNUMBER(FIND($F$1,A2))*1)=1,B2,"")


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Mon, 19 Jan 2015 09:26:21 +0100 schrieb Claus Busch:

in D2 try:
=IFERROR(VLOOKUP("*"&$F$1&"*",A2:B2,2,0),"")
or
=IF(SUMPRODUCT(ISNUMBER(FIND($F$1,A2))*1)=1,B2,"")

or:
=IF(COUNTIF(A2,"*"&$F$1&"*")=1,B2,"")


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

in D2 try:
=IFERROR(VLOOKUP("*"&$F$1&"*",A2:B2,2,0),"")
or
=IF(SUMPRODUCT(ISNUMBER(FIND($F$1,A2))*1)=1,B2,"")


Regards
Claus B.


Thanks Claus.

The SUMPRODUCT formula works best. It returns B correctly in all cases I tested where the others return "" if the match is the leading characters in A.

Appreciate it.

Howard

Not sure why the others return ""

Hi Howard,

Am Mon, 19 Jan 2015 02:33:43 -0800 (PST) schrieb L. Howard:

The SUMPRODUCT formula works best. It returns B correctly in all cases I tested where the others return "" if the match is the leading characters in A.

you don't need SUMPRODUCT.
That is enough to get the result:

=IF(ISNUMBER(FIND($F$1,A2)),B2,"")

In the other formulas you have a placeholder (asterix) also in front of
the substring. If the substring begins with the first character you get
an error.
FIND works in all cases.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

you don't need SUMPRODUCT.
That is enough to get the result:

=IF(ISNUMBER(FIND($F$1,A2)),B2,"")

In the other formulas you have a placeholder (asterix) also in front of
the substring. If the substring begins with the first character you get
an error.
FIND works in all cases.


Yes, works very well. 10,000+ rows,,, in a blink.

Sub Pn_Col_D()
Dim LRow As Long

LRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

Range("G2", Range("G2").End(xlDown)).ClearContents

With Range("G2").Resize(LRow, 1)
.Formula = "=IF(ISNUMBER(FIND($F$1,A2)),B2,"""")"
'.Formula = "=IF(SUMPRODUCT(ISNUMBER(FIND($F$1,A2))*1)=1,B2,"" "")"
.Value = .Value

End With

End Sub


Thanks again.

Howard