Variable Array

[davidstrongarm]

Hi, 2 simple questions...

1)
I have a formula which searches for a value in an array A1:A10.
I also have a cell B1 which contains the number 5.

How do write a formula which will search for a value in a new array which is down 5 rows?

So if B1=5, the formula will search the array A5:A14
Or if B1=7, the formula will search the array A7:A16
if B1=1, the formula will search the array A1:A10
etc...


2)
If I have a large 2D array, is there a simple formula to reply TRUE or FALSE if there are two cells anywhere in the array that are the same?

Thanks in advance,
Dave.

[Paul]

Hi Dave, welcome to the forum.

For the first issue, try:

=ISNUMBER(MATCH("Tom",INDIRECT("A"&B1&":A"&B1+10),0))

Replace "Tom" with the value you're after, or a cell reference to that value.

[davidstrongarm]

Thanks Paul, I got what I wanted with the INDIRECT formula

Anyone have any ideas for the second problem?
Basically I have a table of names, but I would like some sort of error msg to appear if the same name is repeated twice. When I say error msg, it could just be a cell returning TRUE instead of FALSE or something like that.

[DonkeyOte]

Other alternatives for question 1

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

OFFSET like INDIRECT is Volatile - INDEX is semi-volatile in so far as it is only Volatile on the file being opened (thereafter non-volatile).

For Question 2

Assume 2d Array is A1:B100 and blanks are to be discounted:

Please Login or Register  to view this content.

[davidstrongarm]

Thanks Donkey, the OFFSET formula will come in handy.

The Array formula works nicely, though I must confess I have no understanding of how it works! I need to read up on Array formulas first I think.

Cheers.

[DonkeyOte]

I need to read up on Array formulas first I think.

see the link in my sig. to Colin Legg's extensive tutorial

The Array formula works nicely, though I must confess I have no understanding of how it works!

In this instance the function "iterates" each cell within the range specified and where the value is non-blank/non-null it performs a COUNTIF to determine how many times the current "iterated" value appears within the main range.
A MAX is then applied to the array of resulting values ... if the MAX returned exceeds 1 then you know you have at least one instance of a duplicate.

[davidstrongarm]

Great! Thanks so much for the help guys!