Is this equal to ANY of these?

[Futile Crush]

Hey guys, I wanted to know how to find out if the value in one cell equals any one of an item in a list, without using a tedious OR formula for everything or VBA code. If there's any way to do this, I'd be greatly appreciative for you to let me know. .

Just to clarify, or give an example, say I had the concatenated sentence "RuneScape is awesome" (concatenated from three randomly alternating cells, so the sentence is also randomised.) I want to put a formula next to it that asks if the concatenated cell matches any of the (static) list below, and if so, return TRUE or FALSE. I don't need you to tell me how to do an IF formula as I already know, and if there is any such formula that doesn't require VBA code or tedious copying and pasting into an OR formula, it should hopefully return TRUE/FALSE anyway.

Thanks in advance.

 

[VoG]

Try

=ISNUMBER(MATCH(C1,A:A,0))

where C1 contains the text that you want to compare and column A is your list.

 

[Futile Crush]

Thanks for the help VoG, but it's not working. Well at least, it's not producing error messages, but rather it's not actually getting it right, it thinks everything is false regardless if there is a match or not.

 

[Aladin Akyurek]

Hey guys, I wanted to know how to find out if the value in one cell equals any one of an item in a list, without using a tedious OR formula for everything or VBA code. If there's any way to do this, I'd be greatly appreciative for you to let me know. .

Just to clarify, or give an example, say I had the concatenated sentence "RuneScape is awesome" (concatenated from three randomly alternating cells, so the sentence is also randomised.) I want to put a formula next to it that asks if the concatenated cell matches any of the (static) list below, and if so, return TRUE or FALSE. I don't need you to tell me how to do an IF formula as I already know, and if there is any such formula that doesn't require VBA code or tedious copying and pasting into an OR formula, it should hopefully return TRUE/FALSE anyway.

Thanks in advance.

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(List,A2)))

where List is the range housing entries to look for, A2 housing a concatenated result string.

 

[Futile Crush]

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(List,A2)))

where List is the range housing entries to look for, A2 housing a concatenated result string.

Thanks . That's really helpful.

 

[bcvernon]

Thanks for the help VoG, but it's not working. Well at least, it's not producing error messages, but rather it's not actually getting it right, it thinks everything is false regardless if there is a match or not.

You probably didn't use $ in your range field -- $C$1:$C$25 -- so you might be pointing to cells that are blank in your evaluation.

e.g. =isnumber(MATCH(A1,$C$1:$C$25,0))

if you do it without the $ and drag the formula down a column of cells, then your lookup_array range will increment as well as you drag it, eventually taking you beyond the cells you wish to compare your lookup_value to.