With F1 value, look at A, if match or partial match return B

L

L. Howard

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
 
C

Claus Busch

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

Claus Busch

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

L. Howard

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

Claus Busch

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

L. Howard

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top