Excel: using vlookup but with wildcards in an array

Question

I have a list of values ​​in a column on one sheet. On another sheet, I have two columns. One is a list of wildcards, and the other is a different list of values. Next to the column on the first sheet, I want the additional column to contain a formula that checks the value in the first column against wildcards in the second sheet. If a match is found, it should display a value next to this template.

Is there any way to do this? He had been with him for hours, and I can’t get him to work.

Thanks in advance.

Some sample data:

Sheet 1

Column A

randomunnecessarydataUSEFULTHINGS123INFOmoregarbage

morerandomstuffIMPORTANT456junkjunkjunk

IMPORTANT456lotsofmorejunk

morejunkUSEFULTHINGS789INFOgarbage

Column B

<some formula>

<some formula>

"

"

and etc.

Sheet 2

Column A

*usefulthings???INFO*

*important456*

Column B

-

456

, <some formula> A 1 A 2. A 2 , , , , 2 B.

Answer 1

, - ( B1, ):

=IF(MIN(IFERROR(MATCH(Sheet2!$A$1:$A$100,A1,0)*ROW($1:$100),FALSE)),INDEX(Sheet2!B:B,MIN(IFERROR(MATCH(Sheet2!$A$1:$A$100,A1,0)*ROW($1:$100),FALSE))),"")

ctrl + shift + enter.

- 1 , - 2 .

:

=IF(MIN(IFERROR(MATCH(Sheet2!$A$1:INDEX(Sheet2!A:A,MATCH("zzz",Sheet2!A:A)),A1,0)*ROW(Sheet2!$A$1:INDEX(Sheet2!$A:$A,MATCH("zzz",Sheet2!$A:$A))),FALSE)),INDEX(Sheet2!B:B,MIN(IFERROR(MATCH(Sheet2!$A$1:INDEX(Sheet2!A:A,MATCH("zzz",Sheet2!A:A)),A1,0)*ROW(Sheet2!$A$1:INDEX(Sheet2!$A:$A,MATCH("zzz",Sheet2!$A:$A))),FALSE))),"")

, ... , ( B1, , ):

*² =IFERROR(IF(SMALL(IFERROR(MATCH(Sheet2!$A$1:INDEX(Sheet2!$A:$A,MATCH("zzz",Sheet2!$A:$A)),$A1,0)*ROW(Sheet2!$A$1:INDEX(Sheet2!$A:$A,MATCH("zzz",Sheet2!$A:$A))),FALSE),COLUMN()-1),INDEX(Sheet2!$B:$B,SMALL(IFERROR(MATCH(Sheet2!$A$1:INDEX(Sheet2!$A:$A,MATCH("zzz",Sheet2!$A:$A)),$A1,0)*ROW(Sheet2!$A$1:INDEX(Sheet2!$A:$A,MATCH("zzz",Sheet2!$A:$A))),FALSE),COLUMN()-1)),""),"")

, , ( ). , UDF.

2

, C1 ( /) ( B):

=IF(B1="","",IFERROR(IF(SMALL(IFERROR(MATCH(Sheet2!$A$1:INDEX(Sheet2!$A:$A,MATCH("zzz",Sheet2!$A:$A)),$A1,0)*ROW(Sheet2!$A$1:INDEX(Sheet2!$A:$A,MATCH("zzz",Sheet2!$A:$A))),FALSE),COLUMN()-1),INDEX(Sheet2!$B:$B,SMALL(IFERROR(MATCH(Sheet2!$A$1:INDEX(Sheet2!$A:$A,MATCH("zzz",Sheet2!$A:$A)),$A1,0)*ROW(Sheet2!$A$1:INDEX(Sheet2!$A:$A,MATCH("zzz",Sheet2!$A:$A))),FALSE),COLUMN()-1)),""),""))

, ;)

* ² EDIT B EDIT 2 C + EDIT.

3

UDF- VBA- ( alt + F11) "" → "". :

Option Explicit

Public Function getLikeLookup(str As String, rng As Range, Optional nCou As Long, Optional outCol As Range) As String

  'for not case sensitive
  str = LCase(str)

  'set ranges
  If nCou < 1 Then nCou = 1
  If outCol Is Nothing Then Set outCol = rng.Offset(, rng.Columns.Count - 1).Resize(, 1)
  Set rng = Intersect(rng.Resize(, 1), rng.Parent.UsedRange.EntireRow)
  Set outCol = Intersect(outCol.Resize(, 1), outCol.Parent.UsedRange.EntireRow)

  'get check-array (will be faster than running the sheet directly)
  Dim inArr As Variant
  inArr = rng.Value

  'run checks
  Dim i As Long
  For i = 1 To UBound(inArr)
    'If str Like inArr(i, 1) Then nCou = nCou - 1
    If str Like LCase(inArr(i, 1)) Then nCou = nCou - 1 'for not case sensitive
    If nCou = 0 Then Exit For
  Next

  'check for valid output
  If i > UBound(inArr) Or i > outCol.Rows.Count Then Exit Function

  'set output
  getLikeLookup = outCol.Offset(i - 1).Resize(1, 1).Value

End Function

UDF, . .

getLikeLookup(lookup_string,lookup_range,[#_occurrence,[output_range]])

• lookup_string: , ( , )

• lookup_range: lookup_string. output_range , lookup_range.

• #_occurrence: [optional] , . (, 1), , .

• output_range: [] output_range.

( B1):

=getLikeLookup($A1,Sheet2!$A:$B,COLUMN()-1)

( B1):

=IF(A1="","",getLikeLookup($A1,Sheet2!$A:$B,COLUMN()-1))

2 enter.