1

I'm pretty lost with something I have to do with Excel.

I need to make a recursive lookup between two tables to find a match, and insert a specific value in one cell.

In the next example, I show you what should be done, I hope anyone could help! :)

Basically, I need to look for values from column "E" within each "B" cell, and if there is any match, insert the specific "F" value in "C".

Example

Error after increasing range of Trigger column

3rd pic

I don't know if a formula is enough, but maybe a macro is needed.

I would appreciate any help.

Thanks in advance!

Improve this question
2
  • Be careful: when working with recursion in Excel formulas, you might get at "circular reference" error. This link explains how to handle this (or not): support.microsoft.com/en-us/office/… Commented Jul 14, 2021 at 8:20
  • What is your excel version? Commented Jul 14, 2021 at 9:57

2 Answers 2

Reset to default
1

Try this UDF using dictionary. If no match found it will return blank. Instead of parsing the Text column cell it loops through Trigger column's dictionary keys to find match in the Text columns cell value.

dict.CompareMode is set to be vbTextCompare for non-case senseitive comparison to find matches. For case sensitive comparison we can set this to vbBinaryCompare

excelmacromastery Page

Option Explicit

Public Function PartialStrMatch(str As String, matchCol As Range, lookupCol As Range) As String
Dim dict As Object, i As Long, cl As Range
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare

For Each cl In matchCol
    If Not dict.exists(cl.Value) Then
    dict.Add cl.Value, lookupCol(Application.Match(cl, matchCol, 0)).Value
    End If
Next cl

For i = 0 To dict.Count - 1
    If InStr(1, str, dict.Keys()(i), vbTextCompare) > 0 Then
        PartialStrMatch = dict.Items()(i)
        Exit For
    End If
Next i

End Function

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Hi Naresh, thanks for your replay, it works for me but till 10 trigger values. How could I change the function to allow like 10K possible trigger values for example? :S Regards!!
Just change the range in the formula like $E$2:$E$10000 and so for F column.. If you want to say "thank you," vote on or accept that person's answer
I show you what I mean, in the body of my post. Thank you!! :)
thanks for your support, I really appreciate it. What I've seen in my test (i update my post), is that for example, it seems like the trigger starts from below, so for case "Seat" for example, it writes value "Not a car" instead of "Car" because in some place there is an "E", and if I add a trigger called "Z" and the text contains that letter, it writes that value. Any idea?
@CrisoNikon .. code edited with adding "Exit for" in the second loop. Not it will take only the first match... But if you insert E or Z in the first then it will take for those values and not cars
|
0

=IFERROR(INDEX($F$1:$F$3,MATCH(1,--(COUNTIF($B1,"*"&$E$1:$E$3&"*")>0),0)),"No matching values") Wrapping the trigger range in "*" makes it accept all characters before or after the search string

Improve this answer

2 Comments

Hello @P.b, I'm getting almost always value "No matching values" :S Any other idea? Thank you so much for your replay!
I tested it and it spilled wrong results. I adjusted the formula so it doesn't spill and works correctly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.