countif/index match using text string

Hi everyone,

I have the following formula:

=COUNTIF(INDEX('Answer Data Month 1'!$AG$2:$AG$15000,MATCH(LEFT(E7,254),'Answer Data Month 1'!$BC$2:$BC$15000,0)),"Sales")

E7 is a long text string. I understand index/match can work with text strings, but I'm getting a #N/A error evrytime. Column AG has the results I'm looking for, and column BC has the texts I'm searching off of. Any ideas what I'm doing wrong? I'm trying to count the number of times "Sales" is found in AG that's related to text found in BC based on value E7 which is on another worksheet.

Thanks!!

Is this related/similar to this thread
http://www.excelforum.com/excel-form...lue-error.html

Are the values in column BC more than 254 characters, and you're trying to match only the 1st 254 (as we did in the previous thread)
You need to add the * wildcard to the match

MATCH(LEFT(E7,254)&"*",'Answer Data Month 1'!$BC$2:$BC$15000,0)

Jonmo,

Thanks very much. Its work on the same database, but different requirements. In any case, thanks very much for your help!

You're welcome.