How to reference a cell depending on the value in another cell??

[wb198]

Hi, I want to retrieve the data in a a cell in Sheet2, based on the value of
a cell in Sheet1. How do I do this?
For example, in Sheet1, cell A1 has the value "5". In cell A2, I want it to
retrieve the data in cell B5 of Sheet2 (the 5 coming from the value in cell
A1). Thus, if I change the value in cell A1 to 6, cell A2 should then
retrieve the data of the in cell B6 in Sheet2.
The code in cell A2 would be something like:
=Sheet2!B(Sheet1!A1)
but of course that syntax doesn't work!!

 

[carlo]

Hi, I want to retrieve the data in a a cell in Sheet2, based on the value of
a cell in Sheet1.   How do I do this?
For example, in Sheet1, cell A1 has the value "5".  In cell A2, I want it to
retrieve the data in cell B5 of Sheet2 (the 5 coming from the value in cell
A1).  Thus, if I change the value in cell A1 to 6, cell A2 should then
retrieve the data of the in cell B6 in Sheet2.
The code in cell A2 would be something like:
=Sheet2!B(Sheet1!A1)
but of course that syntax doesn't work!!

Use indirect:
in Cell A2
=indirect("Sheet2!B" & A1)

hth
Carlo

 

[T. Valko]

Another one:

=INDEX(Sheet2!B:B,A1)

 

[wb198]

thanks greatly.

now is there a way to go a bit further and have it look for the value in
cell A1 in a column in Sheet2, and then return the value from another cell in
the same row in Sheet2 as the reference value it found?

e.g.
Sheet1:
Cell A1 : Value is '5'
Cell A2 : =INDEX.....

Sheet2:
Cell B1: Value is '7' Cell C1: Value is 'Sheep'
Cell B2: Value is '5' Cell C2: Value is 'Dog'
Cell B3 : Value is '9' Cell C3: Value is 'Cow'


Cell A2 in Sheet1 will thus reference the value of Cell A1 ('5'), look for
it in Column B of Sheet2, find it in Cell B2, and then return the value of
Cell C2 ('Dog').


Thanks!!

 

[T. Valko]

Try this:

=VLOOKUP(A1,Sheet2!B:C,2,0)