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

Question

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?

Gaijinhunter · Accepted Answer · 2012-07-12 10:29:08Z

3

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

edited Jul 12, 2012 at 10:29

answered Jul 5, 2011 at 13:38

Gaijinhunter

14.7k4 gold badges53 silver badges58 bronze badges

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

7 Comments

Sunil Tej Over a year ago

(1)yes,the value to be returned to sheet 1 column 2 is a string as well.(2)yes the value from the column 2 sheet 2 is required against the sentence in which the string is found in sheet 1 column 2(3)I'd like them seperated with an additional column or by a comma

Gaijinhunter Over a year ago

Please note that in the code above, I stopped the range of the glossary to be the last cell in column A that is filled with something. This is to avoid errors with empty cells in the reference table (in which case would always come back as a match).

Sunil Tej Over a year ago

I need the result to be written in sheet 1 column 2.I think what you are doing here is updating the sheet 1 column 1 with result.Could you please tell me how to edit that?

Sunil Tej Over a year ago

I tried to run the function.its giving me a #NAME? in B2 when I tried to run FindString(A2,Sheet2!A:B)

Gaijinhunter Over a year ago

You would write this formula in sheet 1 column 2. The function will return the result you want. As for it giving you the error, this mecro is just a start, so it has some sensativities. Like your table on sheet 2 must have values starting from A1. Does it? Also, make sure you put it in file, save it, then reopen to make sure the macro is active.

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related