Comparing Text from Two Columns Against a Single Cell

[redsunrising15]

Hello all,

I am working with two columns of information, and one additional cell of data.

One column is words and multi-word objects and the other is the acronyms that represent those words.

In the single cell of data is a long list of both words, phrases, and acronyms. My goal is to check the single cell against both columns to make sure that the single cell does not contain both an acronym and what it stands for.

I am trying to search the columns at the same time (the words and acronyms are A2:16, B2:16 respectively, and they correspond to each other.) If the single cell contains both a string from the first column and the second column, I want it to send me an indicator and then tell me which items caused the error.

Currently I am using =IF(ISNUMBER(SEARCH(A2:16, C19)),ISNUMBER(SEARCH(B2:16,C19))) with C19 being the single cell. The function only seems to work on select words, no phrases, and only for the very first pair (A2, B2).

Any help would be appreciated.

 

[Aladin Akyurek]

Try...

=IF(ISNUMBER(SEARCH(" "&VLOOKUP(LOOKUP(9.99999999999999E+307,SEARCH(" "&B2:B16&" "," "&C19&" "),B2:B16),CHOOSE({1,2},B2:B16,A2:A16),2,0)&" "," "&C19&" ")),"word and acronym present at the same time","not both word and acronym")

 

[redsunrising15]

It doesn't seem to be working. It outputs "not both word and acronym" no matter what.

 

[Aladin Akyurek]

It doesn't seem to be working. It outputs "not both word and acronym" no matter what.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][td]word and acronym present at the same time[/td][/tr]
[tr][td]

2​

[/td][td]connecticut[/td][td]CT[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]california[/td][td]CA[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]new york[/td][td]NY[/td][td][/td][/tr]
[tr][td]

5​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

16​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

18​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

19​

[/td][td][/td][td][/td][td]NY New York[/td][/tr]
[/table]

C1 >>

=IF(ISNUMBER(SEARCH(" "&VLOOKUP(LOOKUP(9.99999999999999E+307,SEARCH(" "&B2:B16&" "," "&C19&" "),B2:B16),CHOOSE({1,2},B2:B16,A2:A16),2,0)&" "," "&C19&" ")),"word and acronym present at the same time","not both word and acronym")

 

[redsunrising15]

It is working for me now, thanks!

Is there a way to detect which pair caused the error? My list of words and acronyms is several thousand columns long and would be a complete pain to hunt down by hand.

 

[Aladin Akyurek]

It is working for me now, thanks!

Is there a way to detect which pair caused the error? My list of words and acronyms is several thousand columns long and would be a complete pain to hunt down by hand.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][td]Word and Acronym Error: New York | NY[/td][/tr]
[tr][td]

2​

[/td][td]Connecticut[/td][td]CT[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]California[/td][td]CA[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]New York[/td][td]NY[/td][td][/td][/tr]
[tr][td]

5​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

16​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

18​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

19​

[/td][td][/td][td][/td][td]NY New York[/td][/tr]
[/table]

In C1 enter:

=IF(ISNUMBER(SEARCH(" "&VLOOKUP(LOOKUP(9.99999999999999E+307,SEARCH(" "&B2:B16&" "," "&C19&" "),B2:B16),CHOOSE({1,2},B2:B16,A2:A16),2,0)&" "," "&C19&" ")),"Word and Acronym Error: "&VLOOKUP(LOOKUP(9.99999999999999E+307,SEARCH(" "&B2:B16&" "," "&C19&" "),B2:B16),CHOOSE({1,2},B2:B16,A2:A16),2,0)&" | "&LOOKUP(9.99999999999999E+307,SEARCH(" "&B2:B16&" "," "&C19&" "),B2:B16),"OK")