Nested Search Formula - Need Help

HI Everyone,

I'm in need of some help. I have to figure out a formula that can identify if a cell contains a string of text based on a list of prefixes found in another tab and then have the ability to populate a result from a corresponding cell found in the list tab. So to clarify, I will have two tabs. The first tab or data tab will contain a column of model numbers and various other data we need not be concerned with. The 2nd tab will have a list of of prefixes for the various model numbers with a corresponding price list associated with it. Basically, what I have to do is look at a model number found in the data tab, go into the price list tab and identify if that model number starts with the prefixes provided and if it does fill in the corresponding price list associated with that prefix. I have been playing with the below formula which uses the prefixes found in the price list tab and searches a cells string of text to identify if the cell contains the prefix. Right now it populates a true/false. If the value is true then I want to return the name of the price list associated with that prefix. One other issue I have is that some prefixes contain very similar detail. For instance, one of the prefixes I am identifying in WM and another is WMT. Now if I were to manually compare this to a model number found in the data tab I could easily identify if a model number begins with just a WM or if it has WMT. Right now the formula below will identify with WM and WMT. I hope I explained is correctly but just to be certain I included an attached example of what I am trying to do. I have to do this for thousands of lines of data so I hope we can figure out a solution. Thanks!


=SUMPRODUCT(--ISNUMBER(SEARCH(Prefix,N3)))>0

Or try this ...

=LOOKUP(2,1/COUNTIF(A2,'List Tab'!$A$2:AB$137&"*"),'List Tab'!$B$2:$B$137)

This seems to work but I'm having trouble replicating it in my spreadsheet at work. Could you explain the portion of the formula to me? For instance, I'm not sure what the "2" is doing in the beginning of the formula. Is that referencing a column? Also, what is the purpose of the 1/COUNTIF? I'm not sure what the "1/" is doing in the formula or it's purpose. Additionally, why do you use a range of "$A$2:AB$137"? Why does it extend way beyond the list in my data which is only in columns A and B? And What does "&"*"" do? I feel like if I understood these sections I may be better able to modify this for my spreadsheet at work. Sorry to be a pain. I feel like a complete rookie with this one.