I have an array from B1:K16 that you manually type in text. I want a formula
in each cell down below that array that is the same size and shape that
returns "false" when there is not a match anywhere in the array and "True"
when a match is found.

i.e. Data Array---B1=Tim C1=Bob D1=Sue
B2=Dan C2=Earl D2=Don
B3=Joe B3=Jan D3=Frank

I would need the formula array to look like: B4 would need the formula
corresponding to B1 and C4 to C1, etc.... In reality B4 wouldn't need a
formula because this would be done sequentially B1 would have data inserted
in it first and then C1, D1, B2, C2, D2, etc... The formula only needs to
look back at cells that have data already inserted in them, i.e the formula
in D5 would need to look back to B1:C2
Formula Array----B4=False C4=False D4=False
B5=False C5=False D5=False
B6=False C6=False D6=False

I know this is convuluted and I am sure their is a more concise way of
expressing this, sorry!! If it helps this is for a draft and I just want to
ensure that the same player doesn't get drafted more than once. Thanks.

Use COUNTIF with the name as the variable and check if it returns more
than 1, something like this in B4:

=COUNTIF($B$1:$D$3,B1)1

Then copy this across to D4 and then B4:D4 down to row 6 (in your
example). If you have a duplicate name then you will get TRUE twice,
so that you can change either of them.

Hope this helps.

Pete

On Aug 27, 9:39*pm, Zombie0635
wrote:
I have an array from B1:K16 that you manually type in text. *I want a formula
in each cell down below that array that is the same size and shape that
returns "false" when there is not a match anywhere in the array and "True"
when a match is found.

i.e. *Data Array---B1=Tim C1=Bob D1=Sue
* * * * * * * * * * * * * * B2=Dan *C2=Earl D2=Don
* * * * * * * * * * * * * * B3=Joe * B3=Jan *D3=Frank

I would need the formula array to look like: *B4 would need the formula
corresponding to B1 and C4 to C1, etc.... *In reality B4 wouldn't need a
formula because this would be done sequentially B1 would have data inserted
in it first and then C1, D1, B2, C2, D2, etc... *The formula only needs to
look back at cells that have data already inserted in them, i.e the formula
in D5 would need to look back to B1:C2
* * * Formula Array----B4=False *C4=False D4=False
* * * * * * * * * * * * * * * * * B5=False *C5=False D5=False
* * * * * * * * * * * * * * * * * B6=False *C6=False D6=False

I know this is convuluted and I am sure their is a more concise way of
expressing this, sorry!! *If it helps this is for a draft and I just want to
ensure that the same player doesn't get drafted more than once. *Thanks..

A simple test you could use is COUNTIF
Try in B4: =COUNTIF($B$1:$D$3,B1)
Copy across/fill down to D6 to populate
Duplicates within B1:D3, if any, will display as numbers greater than 1
Any good? hit YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Zombie0635" wrote:
I have an array from B1:K16 that you manually type in text. I want a formula
in each cell down below that array that is the same size and shape that
returns "false" when there is not a match anywhere in the array and "True"
when a match is found.

i.e. Data Array---B1=Tim C1=Bob D1=Sue
B2=Dan C2=Earl D2=Don
B3=Joe B3=Jan D3=Frank

I would need the formula array to look like: B4 would need the formula
corresponding to B1 and C4 to C1, etc.... In reality B4 wouldn't need a
formula because this would be done sequentially B1 would have data inserted
in it first and then C1, D1, B2, C2, D2, etc... The formula only needs to
look back at cells that have data already inserted in them, i.e the formula
in D5 would need to look back to B1:C2
Formula Array----B4=False C4=False D4=False
B5=False C5=False D5=False
B6=False C6=False D6=False

I know this is convuluted and I am sure their is a more concise way of
expressing this, sorry!! If it helps this is for a draft and I just want to
ensure that the same player doesn't get drafted more than once. Thanks.