CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
K Sudbury
K Sudbury

Reputation: 45

Match words from array into a string

I have a cell which contains a description like the one below

"customer enjoys playing cricket and football."

Then on another worksheet I have a list of sports

Cell / Sport

I need iterate through the list of sports and match the first sport in the list and then print it. Here are some examples of what I'd like to return.

Description / Sport (These are defined under 'Name Manager' as 'SPORT_NAMES')

I am able to do it for each cell using the following formula

=IF(ISNUMBER(SEARCH(Sports!A1,Data!A2)), "Tennis", "")

Thanks,

Kieran

Upvotes: 1

Views: 104

Answers (2)

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

=INDEX($A$1:$A$6,MATCH(1,--ISNUMBER(SEARCH($A$1:$A$6,E1)),0)) - This is an array formula (enter the formula by using Ctrl+Shift+Enter while still in the formula bar)

By using a double unary operator you convert the TRUE / FALSE result of ISNUMBER(SEARCH($A$1:$A$6,E1)) to 0 or 1 respectively.

I then use MATCH() as the return value for INDEX() to get the appropriate result (which will always be the first occurrance)

example

Upvotes: 2

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11988

I'm pretty sure there is a clean and nice way of doing this, but at least, this answer will work.

First of all. I strongly encourage you to list the sports as in my screenshot, with an auxiliary column that shows the importante of each sport. Thay way you can reorder as you want or change positions and remember priority:

enter image description here

Then, I used this dirty formula to get the results:

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IF(SEARCH('LIST OF SPORTS'!$B$2;Hoja1!A1)>0;'LIST OF SPORTS'!$B$2);IF(SEARCH('LIST OF SPORTS'!$B$3;Hoja1!A1)>0;'LIST OF SPORTS'!$B$3));IF(SEARCH('LIST OF SPORTS'!$B$4;Hoja1!A1)>0;'LIST OF SPORTS'!$B$4));IF(SEARCH('LIST OF SPORTS'!$B$5;Hoja1!A1)>0;'LIST OF SPORTS'!$B$5));IF(SEARCH('LIST OF SPORTS'!$B$6;Hoja1!A1)>0;'LIST OF SPORTS'!$B$6));IF(SEARCH('LIST OF SPORTS'!$B$7;Hoja1!A1)>0;'LIST OF SPORTS'!$B$7));"No sports found")

And in Sheet1 I get this:

enter image description here

Hope this can help you out.

Upvotes: 2

Related Questions