I agree, INDEX is definitely a better answer to this than INDIRECT, in fact it really is the closest thing to an array element selector that exists in Excel. (NB: there is no element zero as there is in many programming languages, Excel works on rows and columns, so the first one is always 1 in either direction)
Note that you can use the simple version to select the nth element in a one column array:
=INDEX(A1:A100,27) would give the 27th element of A1:A100, ie A27
or a one-row array:
=INDEX(A1:G1,,5) gives the content of the fifth column in the array, so E1 in this case (note the double comma to show no value for the row is provided)
you can do two-dimensional arrays:
=INDEX(A1:D100,27,4) would be the contents of D27 (row 27, column 4 of the array)
=INDEX(C19:X43,5,7) gives I23
Now, if you have a two-dimensional array and only give one parameter, the result will be a whole column or row. While this makes no sens on it's own, it is fine embedded within another function.
=INDEX(A1:C5,1) - returns a #REF error as it does not even understand whether the 1 refers to the row or the column.
=INDEX(A1:C5,1,) - returns #VALUE because it cannot display the resultant arrary in a cell, but note that the extra comma is now explicitly defining that the 1 means the first row.
=SUM(INDEX(A1:C5,1,)) - returns the sum of all the values in A1:C1, the first row of the array.
However, having said all that, you may not need to explicitly select an array element at all. It seems from your example that you are trying to test the value in a known column for the same row as the formula has been entered in. You can do this using the implicit intersection of the range and the current row, so for example, if you enter this formula in cell B13:
=IF(A:A="X","Yes this works","X not found")
then you can copy this to any other row simply by dragging it out and on each row it implicitly means "the element of column A which is on the same row number as this formula".
Note: the range does not have to be a whole column, it could be A4:A104, or even on another sheet, but in every case it will use the cell in the same actual Excel row as the formula.
I'll say that last bit again: If you have the formula in cell B5 using a range A4:A104 you will not get the fifth element of that range (A8) but rather cell A5. If you use the formula in cell B3 against A4:A104 you get an error because the range does not have anything on row 3 (there is no intersection).
So, if your rows are lined up, this implicit cell from a range is really useful (and even cleaner when you use a named range for the column), but if they are not, you need INDEX to get a true replication of a[n] for any element of the array.