Combined formula

[Simon Minder]

Hi all,

I have three cells with a value:

Cell Value
A1 Test1
A2 Test2
A3 Test3

I would like to have another cell where I can enter a value (e.g. 2) and
receive in an additional field the value (e.g. Test2).

How can I add to the formula (e.g. =A...) a variable from another field?

Kind regards,

Simon

 

[Max]

One way
With B1 containing the input number, eg: 2
In C1: =INDEX(A1:A3,MATCH(TRUE,INDEX((RIGHT(A1:A3)=B1&""),),0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik

 

[Simon Minder]

Hi Max,

Thank you very much for your solution. However, I have a problem applying
the formula to my example.

I have three cells with a value:
Cell Value
A1 Test1
A2 Test2
A3 Test3

Let's assume I enter in a cell (B1) the value "1". I would like to see the
value "Test1" in cell (B2). If I enter the vaule "2" I would like to see the
value "Test2" in cell (B2).

The formula would be stored in the cell (B2) and would take the variable
from cell (B1).

Your formula seems to work only with numbers, but not with text in the
selected range (e.g. Test1...Test3).

Kind regards,

Simon

 

[Raz]

try this

put this in B2
="Test"& B1

Hi Max,

Thank you very much for your solution. However, I have a problem applying
the formula to my example.

I have three cells with a value:
Cell Value
A1 Test1
A2 Test2
A3 Test3

Let's assume I enter in a cell (B1) the value "1". I would like to see the
value "Test1" in cell (B2). If I enter the vaule "2" I would like to see the
value "Test2" in cell (B2).

The formula would be stored in the cell (B2) and would take the variable
from cell (B1).

Your formula seems to work only with numbers, but not with text in the
selected range (e.g. Test1...Test3).

Kind regards,

Simon

 

[Simon Minder]

Hi Raz,

Thank you for your advice, but I would like to select with the number in the
field "B1" a value out of the range A1...A3, by adding the number to the
formula. E.g. I type in "1" and the formula in "B2" would change to "=A1" and
display the value from "A1". If I enter the value "2" into the field "B1" I
would like to see "Test2" in the cell "B2" and the formula would be "=A2". I
only want to be able to change the number in the formula e.g. "1" in "=A1" to
a "2" when I enter a number "2" in a cell.

Kind regards,

Simon

 

[Simon Minder]

Got it: =INDIRECT("sheet name"&ADDRESS(G1;G2))

Hi Raz,

Thank you for your advice, but I would like to select with the number in the
field "B1" a value out of the range A1...A3, by adding the number to the
formula. E.g. I type in "1" and the formula in "B2" would change to "=A1" and
display the value from "A1". If I enter the value "2" into the field "B1" I
would like to see "Test2" in the cell "B2" and the formula would be "=A2". I
only want to be able to change the number in the formula e.g. "1" in "=A1" to
a "2" when I enter a number "2" in a cell.

Kind regards,

Simon

 

[Simon Minder]

Got it: =INDIRECT("sheet name"&ADDRESS(G1;G2))

One way
With B1 containing the input number, eg: 2
In C1: =INDEX(A1:A3,MATCH(TRUE,INDEX((RIGHT(A1:A3)=B1&""),),0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik