3

As the picture shows, I have 3 keywords from cell A2 to A4: a, bb, and ccc.

I have 5 words to check, listed in B2:B6: aa, bb, bc, ccand d.

question

I'd like to check, for each word in Column B, that if it starts with one of the keyword. The expected result is shown in Column C.

However I could not come out with a formula for that.

What I've tried

  1. I tried the match function in Column D and E but it can only do an exact match, not a "begin with" logic.

  2. I tried the CountIf function in Column F and G. It actually can do the reverse match - if any keyword start with the word in column B - but it cannot do the match I want.

So, how can I check a range of words if they contain a value from a given keyword list

Improve this question
4
  • athos you killing me with this, I don't like saying that I cannot figure something out in excel, but you would need to store variables to do this I think. This is a piece of cake in VBA. Sorry :(
    – sgp667
    Dec 2, 2014 at 4:24
  • @sgp667 thanks trying :) good news is the answer below works!
    – athos
    Dec 2, 2014 at 6:35
  • Could you please try to find a better title. "how to write such formula?" isn't very meaningful
    – nixda
    Dec 2, 2014 at 7:32
  • @nixda how about now?
    – athos
    Dec 2, 2014 at 9:08

1 Answer 1

Reset to default
4

Input the following array formula (Ctrl+Shift+Enter) into cell C3 and then drag it down. I extended your scenarios just a bit and it performs as expected. 

=SUM(IF(LEFT(B3,LEN($A$3:$A$7))=$A$3:$A$7,1,0))

Array if formula keyword search

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .