Need suggestions on ‘Find’ function variation

DLB20720 · May 11, 2014

I have a worksheet that reads paragraphs from a live feed that I import into Excel. I have successfully written quite a few functions to parse the info but am struggling to find a function for one facet of the info I receive and am hoping someone may be able to offer a suggestion.

The info I import is regarding stock upgrades from various analysts. Part of the information I receive has the name of the analyst or company that issued the ratings change. I have ratings on the analysts themselves and am looking to ‘score’ them in Excel but have not been able to write a function to isolate the name in a manner where I can use it in an Index / Match function. I have ratings on 150 analysts and some names consist of 1 word (4 characters) and some have 3 words with commas and ampersands included. The longest name has 30 characters. One of my biggest problems is that I have several names that begin with the same word (Williams) so I need to include the 2^nd word to make the value unique to be able to identify it with Index / Match. I’ll list 4 values that represent some of the extremes I am dealing with.

HSBC
Keefe, Bruyette & Woods
Williams Capital
Williams Financial Group

So far, I have successfully written a Right / Find function that lists the analysts name in a cell beginning with the first word, but also includes the first 30 characters. I just can’t find a function to isolate enough of the name to make it unique enough for Index / Mach to work. Hopefully someone can offer a suggestion. Thanks!

DLB20720 · May 12, 2014

One additional comment for anyone who might be researching a similar issue in the future and find this solution helpful; I had an array of 150 analysts names, but when I wrote the formula I expanded the absolute row reference ($) to cover 200 rows in anticipation of adding additional names over time. I can't explain why, but this affected the results returned and prevented many of the names from returning as expected. When I narrowed the absolute reference to cover only rows with information, the formula worked perfectly.

Peter_SSs · May 13, 2014

DLB20720 said:
One additional comment for anyone who might be researching a similar issue in the future and find this solution helpful; I had an array of 150 analysts names, but when I wrote the formula I expanded the absolute row reference ($) to cover 200 rows in anticipation of adding additional names over time. I can't explain why, but this affected the results returned and prevented many of the names from returning as expected. When I narrowed the absolute reference to cover only rows with information, the formula worked perfectly.

It is because the formula looks for any of the "analyst range" values being the first thing found in the column A value. Blank cells are treated as zero length strings in this circumstance and are therefore found at the beginning of every column A value.

If you wanted to use a larger range to allow for expansion, try this version.

=IFERROR(LOOKUP(1,SEARCH($E$2:$E$200,A2)/(LEN($E$2:$E$200)>0),$E$2:$E$200),"No analyst found")

DLB20720 · May 13, 2014

Peter, thanks for trying to explain that further.

I understand how the new formula you mentioed would work, but I still don't understand something about including the cells that had a '0' in them (old formula). Roughly 20% of my cells returned the correct analysts name while 80% did not. I don't understand why there wasn't 100% consistency on this.

Need suggestions on ‘Find’ function variation

DLB20720

Board Regular

DLB20720

Board Regular

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

DLB20720

Board Regular

Similar threads

Forum statistics

Share this page

Need suggestions on ‘Find’ function variation

DLB20720

Board Regular

DLB20720

Board Regular

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

DLB20720

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock