How to compare and search columns for text?

**tomlini** · 04-14-2009, 05:41 PM

Hi All,

I recently posted a similar thread which was very well supported but I couldn’t get a solution because my requirements were not clear enough. I would like to try again with a slightly easier and clearer requirement.

I would like a spreadsheet that compares and searches columns for text and returns true if a duplicate is found.

It is similar to a lottery checker except the lines being checked could be a string instead of a single character.

I have attached a spreadsheet which helps explain what I’m trying to achieve.

Basically, a user can sequentially enter a single character to cells F1:F5. These values are then compared to columns A:C. If one of the columns contain the values in column F then return true to G1.

I hope this makes sense and thanks for your interest.

Cheers,
tomlini

**windknife** · 04-15-2009, 03:29 AM

G1 =IF(MAX(MMULT({1,1,1,1,1},IF(ISERROR(FIND(F1:F5,A1:C5)),0,1)))=5,"exists","non-exists")

Remember to press Ctrl+Shift+Enter after inputing formula.

**tomlini** · 04-15-2009, 04:58 AM

Originally Posted by windknife

G1 =IF(MAX(MMULT({1,1,1,1,1},IF(ISERROR(FIND(F1:F5,A1:C5)),0,1)))=5,"exists","non-exists")

Remember to press Ctrl+Shift+Enter after inputing formula.

Windknife - that is exactly what I'm looking for:-)

Thanks very much, it has helped me a great deal and is much appreciated.

**AumP** · 12-16-2012, 06:08 AM

What can be done to make this forula case insensitive?

**Pepe Le Mokko** · 12-16-2012, 08:03 AM

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.