Finding Text in a field

[Barchetta99]

Hi Folks,

Thanks for taking the time to look at my issue.

I have a field,
e.g. this hat is red

Named Range List - contains words in priority order and
1 red
2 hat
3 this
4 test
5 animal
6 word

Named Range Comment - is used by vlookup to pull out the comments i want to make.
1 red - Ruby Red
2 hat - hat on head
3 this - this old house
4
5
6 word - a word by no deed



I am looking for about 40 words.
I'm using this formula
VLOOKUP(INDEX(List,SUMPRODUCT(ISNUMBER(SEARCH(List,I47))*ROW($1:$41))),Comment,2,FALSE)

Answer Returned = value 6 - A Word by no deed.

In using this formula I can find the words and if my list had unique words only it would work.

Instead what is happening is where multiple words are found in a field the sum products add the values together and come up with a new value which is incorrect based on the logic of a priority ordered list.

I'd like the list to stop looking as soon as it finds a word, I'm thinking i probably need to use a vba case statement for each word to get it stop rather than progress?
I am looking at circa 32,000 rows to then create a pivot on the most frequent words based on this hierarchy. I then need to repeat it monthly.

Any thoughts gratefully received.

 

[DanteAmor]

Hi and welcome to MrExcel.

It is an array formula, find the first word from top to bottom of the named range "List".
In the first example: "this hat is red" the first word in named range "List" is red.

If you want another classification or the sheet is very slow with array formulas, then the other option is a macro.

Dante Amor
	A	B	C	D	E	F	G
1	e.g.	Answer		LIST		Comment
2	this hat is red	Ruby Red		red		red	Ruby Red
3	this sweter is black	this old house		hat		hat	hat on head
4	this hat is white	hat on head		this		this	this old house
5	This is a test formula	this old house		test		word	a word by no deed
6	some text	no matches		animal
7				word
Hoja8

Cell Formulas
Range	Formula
B2:B6	B2	=IFERROR(VLOOKUP(INDEX(List,SMALL(IF(ISNUMBER(SEARCH(List,A2)),ROW(List)),1)),Comment,2,0), "no matches")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[DanteAmor]

Considering the data as I put in my example, try the following macro.

Sub answer()
  Dim a As Variant, b As Variant, c As Variant, d As Variant
  Dim dic As Object, i As Long, j As Long
  
  a = Range("A2", Range("A" & Rows.Count).End(3)).Value2
  b = Range("List")
  c = Range("Comment")
  ReDim d(1 To UBound(a), 1 To 1)
  
  Set dic = CreateObject("Scripting.dictionary")
  For i = 1 To UBound(c)
    dic(c(i, 1)) = c(i, 2)
  Next
  
  For i = 1 To UBound(a)
    For j = 1 To UBound(b)
      If InStr(1, a(i, 1), b(j, 1), vbTextCompare) > 0 Then
        d(i, 1) = dic(b(j, 1))
        Exit For
      End If
    Next
  Next
  Range("B2").Resize(UBound(d)).Value = d
End Sub

 

[Barchetta99]

Thanks for your swift response, I hadn't thought of that approach, you learn something new everyday.

Stay safe and have a wonderful Easter break.