Search and Vlookup ( help plz )

I would suggest that you use data validation in column B so that you have consistant entries. you would need to create a table with all possible replies, then you could use the vlookup return whatever data you need.

if you have a sample workbook to upload, we can maybe help get you started?

yes i inderstand that, but you can create a list of (all?) possible comments, that would restrict them from entering just anything. for instance, what if, instead of...
I Called the customer and there was no reply,,,,the tech entered
I Called the customer and there was no answer
I Called the customer and there was no response
I Called the customer and there was no 1 home
I Called the customer and there was no one at home

these are all the same entry, but trying to do a search that would return a "true" in each case would be next to impossible. however, if the tech was only allowed to enter "I Called the customer and there was no reply" then a seach on that is fairly simple. see what im saying?

how many rows of data do you have?

and for each "I Called the customer and there was no reply" type of reply there could be any mumber of variations for the same answer, right?

Try this formula in C2, then copied down:

=IFERROR(LOOKUP(2, 1/(ISNUMBER(SEARCH($X$3:$X$7, B2))), $Y$3:$Y$7), "")


This will give you matches that DO exist. Then you can scan the data looking for variations and expand your X:Y table to include more variations till you get them all.

Letting the techs enter anything is the problem, you'll have to deal with it in some manual way until such time you DO get data validated entries.

Thanks JB, i had no idea how to give the answer he needed, and thanks for the back-up on getting the techs to enter consistant data

Based on the caveat at then end of post #1, he was already trying some variations on this method. But in the end, their is seldom a magic answer to random text matching.

One idea might be to reduce the text needed. For instance just the word "wrong" might be all that is needed for the 'Wrong info" result, no need to have "wrong phone" and "wrong address" when both are being converted to a less specific category "Wrong info" any.

By reducing the text string (carefully) he might get better results just by being clever.