1

So I have two ranges:

Sentences

I am tom
I am Matt
I am John
Johhny is my name
Molly is cool

Names

Polly
Molly
John
Matt
Tom
Ronnie

So i want to compare Names with Sentences, and if a word from Names exists in Sentences I want to print that corresponding name in Result. If none of the words exists in a sentence, i would like the formula to print "other".

Any help here. I have done this through multiple filters and a really big formula. But i would like to stream line this.

Here is the formula I have used so far with no results

=(IF(ISNUMBER(SEARCH(K:K,G19)),K:K))

Tweaking the formula gets me limited results. Some help would be great! Thanks again

Aprillion
  • 21,510
  • 5
  • 55
  • 89
Ravi
  • 11
  • 2
  • Already answered: http://stackoverflow.com/questions/12915215/excel-check-if-cell-value-exists-in-column-and-then-get-the-value-of-the-next Use ifexists or vlookup – jer_bear Dec 15 '14 at 21:51
  • Are you opposed to using VBA? – peege Dec 15 '14 at 21:58
  • so, for each sentence, if at least 1 name from the list of names is present, you want to print the 1st name? or all names? the former should be (barely) possible with formulas, the letter might require VBA. – Aprillion Dec 15 '14 at 23:28
  • @jer_bear there is no `ifexists` in Excel. – Aprillion Dec 17 '14 at 09:21

1 Answers1

0

To find the 1st name, you can use following array formula (entered by Ctrl+Shift+Enter):

=IFERROR(
  INDEX(names, MIN(MATCH(TRUE, ISNUMBER(SEARCH(names, [@Sentences])), 0))),
  "other")

Explanation:

  1. SEARCH(names, [@Sentences]) will compare each name in the range "names" to the current sentence

  2. MIN(MATCH(TRUE, ISNUMBER(...), 0)) will find the 1st matching name

  3. INDEX(names, ...) will retrieve the name

  4. IFERROR(..., "other") will take care of the no-match-found situation

Please used finite ranges like K$2:K$999 instead of K:K for names, array formulas are not optimized very well for whole-column (or -row) ranges.

Note that [@Sentences] is an Excel Table notation, it should be equivalent to G19 from your question if the cell G19 contains a single sentence.

Community
  • 1
  • 1
Aprillion
  • 21,510
  • 5
  • 55
  • 89