Finding strings in vlookup value, embedded in larger strings within the table array

[wetbean]

I need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array

Essentially I imagine this involves the FIND funtion at some point.

Attached is an incredibly simplified example of what I’m looking for.

Thanks in advance, for your help

[zbor]

Try this:

=INDEX($G$3:$G$10,MATCH(MIN(SEARCH(B3,$F$3:$F$10&B3)),SEARCH(B3,$F$3:$F$10&B3),0))

Comfirm with crtrl+shift+enter (not just enter)

[wetbean]

Hi,

Thanks for the fast response. It works perfect on the example.

Apologies but I should have provided a better example that was truer to my specific problem.

Attached, is my updated example to reflect my real problem (im not trying to return a number so SEARCH() doesn't work)

Again, help is greatly appreciated.

Kind regards,
Zak

[zbor]

1. Comfirm formula with CSE
2. you can achive that by VBA code

[wetbean]

1. I was using CSE incorrectly. Apologies.
2. Is there a separate forum area for requesting help with VBA code?

[zbor]

It's OK staying here, hope someone can figure way out

[wetbean]

Okay great.

For anyone visiting with VBA knowledge, please see my most recently uploaded example.

Basically -

I need my existing Index/Search formula to use an 'alrea.dy exists in the cell above' logic when grabbing a value from column G and putting it in column C(see example, attached)

[zbor]

Here, try this:

Sub Partial_lookup()
Dim ws As Worksheet
Dim rng1 As Range, rng2 As Range
Dim LastRng1 As Long, LastRng2 As Long
Dim i As Integer

Set ws = Sheets("Sheet1")
LastRng1 = Range("B65536").End(xlUp).Row
LastRng2 = Range("F65536").End(xlUp).Row

Set rng1 = ws.Range("B3:B" & LastRng1)
rng1.Offset(0, 1).ClearContents

For Each Rng In rng1
    For i = 1 To LastRng2
    
    If InStr(ws.Range("F2").Offset(i), Rng) > 0 Then
        If Application.WorksheetFunction.CountIf(rng1.Offset(0, 1), ws.Range("F2").Offset(i, 1)) = 0 Then
        Rng.Offset(0, 1) = ws.Range("F2").Offset(i, 1): Exit For
        End If
    End If
    Next i
Next Rng

End Sub

[zbor]

Only be careful: It looks for exact string.
In your original example there was criteria Authorise Customer Bill Invoice Adjustment with space at the end.
So it won't find it in Filenames.

You can ignore spaces with something like this:

Sub Partial_lookup()
Dim ws As Worksheet
Dim rng1 As Range, rng2 As Range
Dim LastRng1 As Long, LastRng2 As Long
Dim i As Integer

Set ws = Sheets("Sheet1")
LastRng1 = Range("B65536").End(xlUp).Row
LastRng2 = Range("F65536").End(xlUp).Row

Set rng1 = ws.Range("B3:B" & LastRng1)
rng1.Offset(0, 1).ClearContents

For Each Rng In rng1
    For i = 1 To LastRng2
    
    If InStr(Trim(ws.Range("F2").Offset(i)), trim(Rng)) > 0 Then
        If Application.WorksheetFunction.CountIf(rng1.Offset(0, 1), ws.Range("F2").Offset(i, 1)) = 0 Then
        Rng.Offset(0, 1) = ws.Range("F2").Offset(i, 1): Exit For
        End If
    End If
    Next i
Next Rng

End Sub

Edit: Used Trim as JP suggested

[JosephP]

using trim might be safer. btw for the original question I reckon vlookup with a wildcard would be easier
=vlookup("*"&b3&"*",$f$3:$g$10,2,false)