Find in Array

[astro_al]

Howdy - another rediculously simple question for you:

If I have an Array and I want to know which row number of a specific column contains (for example) a particular value, or text like 'A10', which is unique in the array, is there an elegant-ish way of doing it? I want it to return the row number where 'A10' appears in the array.

Thanks for any help, Al.

[Domenic]

Assuming that your data starts in the second row of Column A...

For the row number within Column A:

=MATCH("A10",A:A,0)

For the position of the lookup value within the range of cells, relative to the first cell in the range:

=MATCH("A10",A2:A100,0)

Hope this helps!

Howdy - another rediculously simple question for you:

If I have an Array and I want to know which row number of a specific column contains (for example) a particular value, or text like 'A10', which is unique in the array, is there an elegant-ish way of doing it? I want it to return the row number where 'A10' appears in the array.

Thanks for any help, Al.

[astro_al]

Thanks for the help. I have used the above in the following way:

TEMP2 = Match(TEMP4, FAIL_ARRAY(1:56, 80),0)

(I.e. I have tried to apply the idea to an array called 'FAIL_ARRAY', in column 80.

TEMP4 is what I am trying to find in that column of the array. Apparently I have a syntax error (no surprise there...) - any ideas? There doesn't seem to be any help files for 'MATCH'...

Thanks again, Al.

[Domenic]

To find the the position of the text TEMP4 in column 80 (Column CB)...

=MATCH("TEMP4",CB:CB,0)

OR

=MATCH("TEMP4",CB1:CB56,0)

...which will return a number.

If you only want to check whether your lookup value exists in the column...

=ISNUMBER(MATCH("TEMP",CB:CB,0))

...which will return TRUE or FALSE.

Also, the MATCH function is well documented in the help files. Check again for a detailed explanation of how the function works.

Hope this helps!

Thanks for the help. I have used the above in the following way:

TEMP2 = Match(TEMP4, FAIL_ARRAY(1:56, 80),0)

(I.e. I have tried to apply the idea to an array called 'FAIL_ARRAY', in column 80.

TEMP4 is what I am trying to find in that column of the array. Apparently I have a syntax error (no surprise there...) - any ideas? There doesn't seem to be any help files for 'MATCH'...

Thanks again, Al.

[astro_al]

Ok, thanks again, but its still not working for me.

To clarify, TEMP4 is a variable, and I want to search column 80 of my ARRAY for the current value of TEMP4. I want TEMP2 to hold the value of the row number where TEMP4 occurs in the array column.

Does it make a difference that I am talking about 2D arrays and you are talking about worksheet cells?

I have tried:

TEMP2 = Match(TEMP4, FAIL_ARRAY(1:56, 80),0)

and,

TEMP2 = Match(TEMP4, FAIL_ARRAY(1, 80): FAIL_ARRAY(56, 80),0)

Surely this should work for both cells and arrays??? I just keep getting syntax errors... I would have thought looking for a value in an array and being able to return its position was pretty basic and fundamental, no...?

Help! Al.

[Domenic]

The MATCH function doesn't accept two-dimensional arrays. So you can use one of the following...

For a specific range of cells...

=MATCH(TEMP4,CB1:CB56,0)

For the whole column...

=MATCH(TEMP4,CB:CB,0)

For a named range...

=MATCH(TEMP4,New_Range,0)

...where New_Range is a one-dimensional range of cells.

Hope this helps!

Ok, thanks again, but its still not working for me.

To clarify, TEMP4 is a variable, and I want to search column 80 of my ARRAY for the current value of TEMP4. I want TEMP2 to hold the value of the row number where TEMP4 occurs in the array column.

Does it make a difference that I am talking about 2D arrays and you are talking about worksheet cells?

I have tried:

TEMP2 = Match(TEMP4, FAIL_ARRAY(1:56, 80),0)

and,

TEMP2 = Match(TEMP4, FAIL_ARRAY(1, 80): FAIL_ARRAY(56, 80),0)

Surely this should work for both cells and arrays??? I just keep getting syntax errors... I would have thought looking for a value in an array and being able to return its position was pretty basic and fundamental, no...?

Help! Al.