Switch to a better formula to just manage a list and not a formula

[VectorW2]

The private sub below works ok, but I am managing the formula and coding. I don't know how to change my current code to something that works off of Sheet2 so I would only manage a list. I have tried Index, Match, and Xlookup, etc., but I haven't figured it out. Additionally, besides always have to update the vba coding, the formula has grown too big. I really need something different, something better. Thank you in advance for your help!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$2" Then
Select Case Target.Value
Case "Run":
Range("B2:B" & Cells(Rows.Count, "C").End(xlUp).Row).FormulaR1C1 = "=IF(OR(COUNTIF(RC[-1],""*""&""IBM""&""*"")>0,COUNTIF(RC[-1],""*""&""multifunction""&""*"")>0,COUNTIF(RC[-1],""*""&""xerox""&""*"")>0,COUNTIF(RC[-1],""*""&""konica""&""*"")>0,COUNTIF(RC[-1],""*""&""ricoh""&""*"")>0),""MFC"",IF(OR(COUNTIF(RC[-1],""*""&""chair""&""*"")>0,COUNTIF(RC[-1],""*""&""desk""&""*"")>0,COUNTIF(RC[-1],""*""&""file cabinet""&""*"")>0,COUNTIF(RC[-1" & _
"],""*""&""bookshelves""&""*"")>0,COUNTIF(RC[-1],""*""&""table""&""*"")>0),""O"",IF(OR(COUNTIF(RC[-1],""*""&""computer""&""*"")>0,COUNTIF(RC[-1],""*""&""laptop""&""*"")>0,COUNTIF(RC[-1],""*""&""desktop""&""*"")>0,COUNTIF(RC[-1],""*""&""printer""&""*"")>0,COUNTIF(RC[-1],""*""&""keyboard""&""*"")>0,COUNTIF(RC[-1],""*""&""mouse""&""*"")>0,COUNTIF(RC[-1],""*""&""monitor" & _
"""&""*"")>0,COUNTIF(RC[-1],""*""&""server""&""*"")>0,COUNTIF(RC[-1],""*""&""router""&""*"")>0,COUNTIF(RC[-1],""*""&""motum""&""*"")>0,COUNTIF(RC[-1],""*""&""POS""&""*"")>0,COUNTIF(RC[-1],""*""&""time clock""&""*"")>0,COUNTIF(RC[-1],""*""&""WiFi""&""*"")>0),""C"",IF(OR(COUNTIF(RC[-1],""*""&""cutter""&""*"")>0,COUNTIF(RC[-1],""*""&""lift truck""&""*"")>0,COUNTIF(RC[-1" & _
"],""*""&""stretch wrap""&""*"")>0,COUNTIF(RC[-1],""*""&""table saw""&""*"")>0,COUNTIF(RC[-1],""*""&""continuous feed""&""*"")>0,COUNTIF(RC[-1],""*""&""fork lift""&""*"")>0,COUNTIF(RC[-1],""*""&""folder""&""*"")>0),""M"",""??""))))"
End Select

End If
End Sub

 

[hagia_sofia]

Hello, please test the following:

=MAP(A2:A20,LAMBDA(x,
LET(
item,Sheet2!A2:A29,
code,Sheet2!B2:B29,
f,ISNUMBER(SEARCH(item,x))*LEN(item),
XLOOKUP(1,(f>0)*(f=MAX(f)),code,"??"))))

To make it work it would probably be a good idea to transform the table on Sheet2 to an official Excel table. Also, there still might be some mismatches, e.g. "Dewalt Tables Saw" would be categorized under O even though it should be M.

 

[VectorW2]

Hello, please test the following:

=MAP(A2:A20,LAMBDA(x,
LET(
item,Sheet2!A2:A29,
code,Sheet2!B2:B29,
f,ISNUMBER(SEARCH(item,x))*LEN(item),
XLOOKUP(1,(f>0)*(f=MAX(f)),code,"??"))))

To make it work it would probably be a good idea to transform the table on Sheet2 to an official Excel table. Also, there still might be some mismatches, e.g. "Dewalt Tables Saw" would be categorized under O even though it should be M.

I must be doing something wrong. The worksheet didn't like it when I put this code into VBA. And when I put it as just a formula, my excel froze up completely.

 

[hagia_sofia]

Thanks for the feedback. What happens if the formula is limited to one cell?

=LET(
item,Sheet2!A2:A29,
code,Sheet2!B2:B29,
f,ISNUMBER(SEARCH(item,A2))*LEN(item),
XLOOKUP(1,(f>0)*(f=MAX(f)),code,"??"))

 

[VectorW2]

It spills, I am not able to make this work. I don't know what I am doing wrong. I am trying to search the limited text listed in Sheet2's column A on Sheet1's column A, (Sheet1's text comes in all different order, with numbers with other detail, misspelled, etc.). If found, then enter the coding letter from Sheet2's column B. If not enter "??". We get up to 140 thousand rows of data, and upwards of 80 thousand sheets I have to check, in only a month and a half. If I could find the best way to do this that would be great. I don't know if match, index, xlookup, or some other form will do the trick. So far, I have had the best luck with the formula using an if statement and the count if, but this is not sustainable.

 

[VectorW2]

Limiting it to one cell is the way to go! That seems to work, perfectly! thank you!