Is this equal to ANY of these?

Futile Crush

New Member
Joined
Oct 3, 2009
Messages
45
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. :cool:
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Try

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

where C1 contains the text that you want to compare and column A is your list.
 
Upvote 0
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.
 
Upvote 0
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. :cool:

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

where List is the range housing entries to look for, A2 housing a concatenated result string.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top