CODEWITHSUNDEEP
excelexcel-formula
Ullas
Ullas

Reputation: 11

Is there a formula to select the active cell in which the formula is being entered?(Without VBA)

I have a formula like this entered to cell D5.

=VLOOKUP(OFFSET(D5,0,-1),Sheet1!$A$2:$B$13,2,FALSE)

In place of D5 next to OFFSET function, I want to put something such that it selects the cell in which the formula is being entered.

In my case,

enter image description here

Here, I want to change value in cells, D5, D7, D9, D11, D13, D15, D17, D19, D21, D23 and G5, G7, G9, G11, G13, G15, G17, G19, G21, G23 with respect to the value entered in the cell to the left.

It can be easily achieved by using VLOOKUP function but I would need to change the reference cell manually for rest of the cells.

The idea is that in this formula, =VLOOKUP( --here-- ,Sheet1!$A$2:$B$13,2,FALSE), instead of using C5, C7, C9, etc, I need a formula to to reference the cell left to the active cell.

Upvotes: 0

Views: 361

Answers (1)

siggi_pop
siggi_pop

Reputation: 637

The answer: In this Vlookup formula the lookup value refers to it's own cell minus one column. ie. the cell to the left.

=VLOOKUP(INDIRECT(ADDRESS(ROW(),COLUMN()-1,4,1)),Sheet1!$A$2:$B$13,2,FALSE)

But actualy: It shouldn't be necessary to use a formula like this! You should be able to just point to C5 once, and then let excel figure out the relative position of the cell to the left, once you copy the formula to other cells: (D7, D9, D11 etc..).

=VLOOKUP(C5,Sheet1!$A$2:$B$13,2,FALSE)

Upvotes: 0

Related Questions