Lookup string from array of strings against an array of strings in Excel

I have two Excel sheets in a workbook.

I have the reference table in one sheet. I need to find if a certain string exists in the cell (which has a sentence) and look up the value against that string in reference table and write it.

This is what I'm trying to do: (Sheet 1 is the operation sheet; Sheet 2 is the reference sheet.)

VLOOKUP(FIND{"compare","contrast","x",..},from the sheet 1 column 1),if string exists,the value against that string in sheet 2 column 2 written in sheet 2 column 2)

{"compare","contrast"} are all words in sheet 1 column 1

I want to compare if any of the strings in the Sheet 2, Column A match with the string (in the sentence or array of strings) in Sheet 1, Column A. Then, if they match, the value against the string in Sheet 2, Column 2 should be generated at Sheet 1, Column B.

开发者_运维问答

Can you please guide me how to write a macro for this?

---

UPDATED:

Here is the function.

It takes 2 parameters: 1st is the cell you want to search (sheet 1, A1), and second is the columns that make up the reference table (sheet 2, A:B). It will take all the terms in Sheet 2 A and make a varriant array glossary out of them, with column A being the key and B being the value. If it finds one of the strings in the cell it'll put it in a new string called result. As a personal choice, I made the glossay a Static so it would run faster in the case that you run this function over many cells at once, but you can change it to Dim if you prefer.

So for A1, you'd write:

=FindString(A1,Sheet2!A:B)

Here's the code, please try it out and I hope it helps, or at least gives you a good start.

Function FindString(ByVal text As String, _
                    ByVal term_list As range) As String

Dim result As String
Dim i As Long
Static glossary As Variant
glossary = range(term_list.Cells(1, 1).End(xlDown), term_list.Cells(1, 2))

For i = 1 To UBound(glossary)
    If InStr(text, glossary(i, 1)) <> 0 Then
       result = (glossary(i, 1) & " = ") & (glossary(i, 2) & vbLf) & result
    End If
Next

If Len(result) <> 0 Then
    result = Left$(result, (Len(result) - 1))
End If

FindString = result

End Function