Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How can I select a cell using subscript like in most programming languages - a[x]?

That is, I don't know which row's data I want but I know the column. This kind of thing can probably be useful in validation formulas like following

= IF(A[row()] = "X", TRUE, FALSE) 
share|improve this question
add comment

5 Answers

up vote 2 down vote accepted

Use the INDIRECT function:

=INDIRECT("A"&C2)

if C2 is the cell that contains the row number .

You can use this in your IF formula:

= IF(INDIRECT("A"&C2) = "X"; TRUE; FALSE)
share|improve this answer
    
Thanks the pointer to INDIRECT. The answer, in this case, will probably be =IF(INDIRECT(ADDRESS(ROW(),1)) = "X"; TRUE; FALSE) –  Holysmoke Sep 14 '09 at 12:30
add comment

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) and

=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. So: =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.

share|improve this answer
add comment

You could try

=if(A & trim(text(something that evaluates to a number))="X",true,false)

I guess, if you could be a bit more specific with how your getting row() it would be helpful.

share|improve this answer
add comment

If you know the range that the values are in use the INDEX function.

e.g.

= IF(INDEX(A:A, ROW()) = "X", TRUE, FALSE)

Unlike the INDIRECT function it is not volatile and will hence not be recalculated whenever anything changes.

share|improve this answer
add comment

Here are two more options for you:

Turn on the R1C1 reference style

In Excel 2003 for example:

  1. Click Options on the Tools menu, then click the General tab.
  2. Under Settings, select the R1C1 reference style check box.

Then in your formula, you would just do this:

=R1 = "X"

which will automatically return a true or a false. Please note that in your formulas, any time you use If() to return true and false, you can just get rid of the If() entirely as any comparison already returns a boolean value.

Unfortunately, with this method, you can't use the A1 reference style, so this trick may be of limited utility for you.

Use named ranges

Named ranges really are powerful, and they are easy to use. Let's say you've labeled the column with the X's in it Flag. So you would select the entire column and select Insert -> Name -> Define. Give it the name Flag (if it hasn't already found it based on your label) and then in formulas you use the word Flag as if it was a cell reference, like so:

=Flag = "X"

This will use the value from the same row, but the correct column.

In addition to manually defining names, if you want to add labels to a whole set of data at once, select your data range, then choose Insert -> Names -> Create, and choose the position of the labels (usually Top Row for me). Now you've defined a named range for every column in your spreadsheet and you can begin using those in your formulas.

One more tidbit

Don't miss out on the intersect operator, which is a space. The union operator is a comma as in =Sum(A1, B1) and the intersect operator is similarly used like this =Sum(Flag 2:10). If the named range Flag referred to column 1 for example, then this would address the range A2:A10, the intersection of the two listed ranges. You can use many intersections one after the other as in range range range range. A:A 1:1 resolves to the same reference as A1.

If your row data is also labeled meaningfully, as in regions or years or other values, then adding named ranges and using the intersect operator can do some wonderful things, such as, if the named range TotalSales refers to a column with sales amounts in it, and the named range California refers to all the (entire) rows with California's data—not necessarily contiguous—then the formula =Sum(TotalSales California1) would be the sum of the intersection of those two.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.