# Build a cell reference without knowing Row # (number)

This can't be a more basic spreadsheet question but I have always been a SQL kinda guy...

How do I build a formula where a value will be pulled from another column on the current row. Can't be the conventional 'E2' because the current row might be 3.

The problem boils down to defining a formula where the `ROW` designator is relative. `\$E2` works when the formula is evoked on Row #2 but fails when evoked from Row #3. I'm creating Rows thru the API - my code can't know what row number is being inserted.

• You might want to state what program you're using.
– Daniel Beck
Commented Jun 2, 2011 at 13:20
• googleDocs' spreadsheet but i'll eat my hat if it's different than excel. Commented Jun 2, 2011 at 13:52

## 5 Answers

The `ROW` function returns the row number of any cell or range.

The syntax is: `Row( reference )` but if the reference parameter is omitted, it assumes the cell in which the Row function was entered.

This can be used to refer to cells using `indirect`, for example `=indirect("C" & row())` refers to column C in the current row.

`=R[0]C[-1]` would make sense but R1C1 notation isn't working in the new version of Google Sheets except within the INDIRECT(ref, FALSE) function, e.g. `INDIRECT("R1C1", FALSE)`.

So write:

``````=INDIRECT("R[0]C[-1]", FALSE)
``````
• Parameter separation would be through ';' not through ','.
– Jpsy
Commented Feb 12, 2016 at 12:45
• @Jpsy Depends on your locale. It is `,` for some countries and `;` for others.
– user79865
Commented May 11, 2016 at 0:58
• Whow @sandwich – that explains constant hassles I had with the docs. AFAIK even Excel has this difference depending on the locale. Surely a big problem for many users who run into this problem without understanding why their commands do not work as described in the docs. Thanks for the explanation!
– Jpsy
Commented May 20, 2016 at 6:35

The `\$` in excel (and google doc's spreadsheet) is used to create absolute (non relative) references. In other words, `\$e\$3` will refer to e3 no matter where it is copied to, from anywhere its copied to. You must lock the column and row individually (or the other one will change).

• Also applies to Apple Numbers.
– Daniel Beck
Commented Jun 2, 2011 at 13:29
• [sry for the misclicks on the votes] I'm looking for the Absolute Column Relative Row expression. Always want value of E from row relative to current row. Commented Jun 2, 2011 at 13:51
• \$e2 or whatever number you want there. Commented Jun 2, 2011 at 13:54
• isn't '2' absolute? I need a way to specify 'current row'. Commented Jun 2, 2011 at 16:17
• The 2 is not absolute, just the e is. The \$ only locks the thing that follows it immediately, not the whole thing. Commented Jun 2, 2011 at 16:22

This actually works and doesn't change the offset when inserting or removing rows or columns. The reverence here gets the value from 3 rows up and 5 cells left:

``````=INDIRECT(CONCATENATE("R", ROW() - 3, "C", COLUMN() - 5), FALSE)
``````

Change the values after `ROW()` and `COLUMN` to +/- the offsets you desire.

I needed a lookup to a separate sheet based on the value in same row and one column to the left. My problem was that I wanted to cut and paste text for the formula anywhere in any sheet, row, or column and have it work the same.

This is the `LOOKUP` formula I wanted and am using currently with success:

``````=IF(INDIRECT(CONCATENATE("R", ROW(), "C", COLUMN() - 1), FALSE) = "", "", VLOOKUP(INDIRECT(CONCATENATE("R", ROW(), "C", COLUMN() - 1), FALSE), Lookups!\$A\$2:\$B\$20, 2, TRUE))
``````

Use:

``````=R[0]C[-1]
``````

The square brackets mean it is a relative reference. `=R1C1` is the same as `=A1`

this works in google docs, not sure about excel.

• R[1]C[-1] does not = A1. Were you intentionally referencing a different cell with the usage of brackets? Commented Jan 9, 2013 at 1:27
• typing =R[0]C[-1] in cell B1 is the same as typing =A1. In Google docs it will auto convert one to the other.
– eddy
Commented Jan 15, 2013 at 3:11