Change The ROW / COLUMN from a formula

[JhonsonTan]

C8 = "20" , K8 = "$1000" , K22 = "$2000"
if c3 = "=c8" , THEN c2 = k8 = "$1000"
if c3 = "=c22" , then c2 = k22 = "$2000"
so if i just need to change c3 value , then c2 will follow
is this possible?
i want the formula in C2 cell
thank you

 

[JhonsonTan]

I don't know why you would need to use INDIRECT and FORMULATEXT. What's in D3?

lol.. i also dont know why i used it, i just try to get the solution.
sorry the formula should be
=INDEX($DR4:$DR10000,MATCH(INDIRECT(RIGHT(FORMULATEXT(c3), LEN(FORMULATEXT(c3))-1)),D4:D10000,FALSE)))
c3 = "=c#"
i use formulatext to get the formula from c3 which is "=c#" , then i cut the "=" with LEN , at which i get "C#" , then i indirect it so i get the exact value.
its a bad formula, isnt it

 

[JhonsonTan]

=INDEX($DR4:$DR10000,MATCH(INDIRECT(RIGHT(FORMULATEXT(c3), LEN(FORMULATEXT(c3))-1)),c4:c10000,FALSE)))
sorry this is the correct one i think

 

[Andrew Poulsom]

Isn't this:

=INDEX($DR4:$DR10000,MATCH(INDIRECT(RIGHT(FORMULATEXT(C3), LEN(FORMULATEXT(C3))-1)),C4:C10000,FALSE))

the same as?

=INDEX($DR4:$DR10000,MATCH(C3,C4:C10000,FALSE))

If you want use the row number referenced by C3:

=INDEX($DR4:$DR10000,ROW(INDIRECT(SUBSTITUTE(FORMULATEXT(C3), "=","")))-ROW(DR3))

 

[JhonsonTan]

Isn't this:

=INDEX($DR4:$DR10000,MATCH(INDIRECT(RIGHT(FORMULATEXT(C3), LEN(FORMULATEXT(C3))-1)),C4:C10000,FALSE))

the same as?

=INDEX($DR4:$DR10000,MATCH(C3,C4:C10000,FALSE))

If you want use the row number referenced by C3:

=INDEX($DR4:$DR10000,ROW(INDIRECT(SUBSTITUTE(FORMULATEXT(C3), "=","")))-ROW(DR3))

wow this is exactly what i want. is there anything u cant solve?
can u explain to me why there is -ROW(DR3)
if i use (D:D) as the reference for INDEX, should i add -ROW(DR3) ?

 

[Andrew Poulsom]

Because your indexed range reference starts at row 4 you need to deduct 3. If you start at row 1 or reference the entire column you don't need to deduct 3.

 

[JhonsonTan]

Because your indexed range reference starts at row 4 you need to deduct 3. If you start at row 1 or reference the entire column you don't need to deduct 3.

ok i got it.. thanks
if c3 its a bit complex formula, how do i do it? using the row reference
c3 = "=LOOKUP(REPT("z",255),A:A)"

 

[Andrew Poulsom]

To return what's in column B:

=LOOKUP(REPT("z",255),A:A,B:B)

 

[JhonsonTan]

To return what's in column B:

=LOOKUP(REPT("z",255),A:A,B:B)

can you include c3 in the formula? because im using c3 as the reference for the others

 

[Andrew Poulsom]

Like this?

=INDEX(B:B,MATCH(C3,A:A,FALSE))

 

[JhonsonTan]

Like this?

=INDEX(B:B,MATCH(C3,A:A,FALSE))

Whats the different between vlookup and index? which one is better?

i have this case
a1 = 1 | b1 = a1 | c1 = a2 | d1 = a5 | e1 = a6
a2 = 2 |
a5 = 3 |
a6 = 10|

i want c1 formula, that can be drag to d1,e1,f1,etc
is it possible?