search and lookup formula

**thompssc** · 02-07-2012, 06:41 PM

Ok, the solution is probably easier than I think, but I can't come up with a way to do this.

Basically, I have a table with over 20,000 rows of data, and one particular column has the comments left by users who input the journal entries. From what I've been told, these people had been clearly instructed to leave comments on every entry and to do so in a specific way. From looking at the comments, this looks to be the case. What I have to do is create a new column that lists the customer for each transaction. I have a list of most of our customers in a table on another worksheet. What I want to do is to write a formula that searches the adjacent cell for any value in that table, and if it contains any of those values, return that value from the table, and if not, "". The problem I'm encountering is #VALUE!. If I try and write an IF(SEARCH("CustNameHere",M2)>0,"CustNameHere",""), I receive the aforementioned error due to it not finding that string within the cell. Of course, doing it that way would require me nesting a bajillion If's, which wouldn't be fun. I'm thinking there's got to be some way to do this using Vlookup/Match/Index or some sort of table/lookup function, but I can't think of how I need to do this. Can anyone help unstick the gears in my brain?

Thanks,

Scott

**Haseeb Avarakkan** · 02-07-2012, 06:50 PM

Hello Scott,

try to use SEARCH with ISNUMBER, & see if this works for you.

=IF(ISNUMBER(SEARCH("CustNameHere",M2)),"CustNameHere","")

**tigeravatar** · 02-07-2012, 07:16 PM

thompssc,

Attached is an example workbook based on the criteria described. In Sheet1 is the list of records (I only made this one 500 rows) with column M being the comments. Mixed in the comments are some random names. On Sheet2 is the Customer List. The formula in Sheet1 cell N2 and copied down is:
=IFERROR(INDEX(Sheet2!$A$2:$A$15,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(Sheet2!$A$2:$A$15,M2)),),0)),"")

What that formula does is look for any matches from the customer list within the Comment in the M cell. If it finds one, it returns that name. Otherwise it is blank.

**tigeravatar** · 02-07-2012, 07:18 PM

Alternatively, since you mentioned the comments are supposed to be in a specific format, you may be able to extract the name instead of searching for a match from a list of names. If you could provide some examples of what the comments look like, we can provide a formula to extract the name from the comment directly.