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

  • Thread starter Thread starter L. Howard
  • Start date Start date
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
 
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 = ""
Click to expand...

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.
 
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,"")
Click to expand...

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


Regards
Claus B.
 
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.
Click to expand...


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.
Click to expand...

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.
 
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.
Click to expand...

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