How to "go to" cell referenced in another cell

[BEEJAY]

Greetings:
Excel 2003:
Using example below:
A1 is manually entered - in this case, as +B4
Need a formula in cell A6, that would "go to" the cell referenced in A4,
then select the data from column C, ( 118 ).
In other words, Cell A6 should:
Read cell reference indicated in A4,
Then "go to" B4,
then enter value one column to right.
I'm trying variations of:
=Range("A1").offset(0,1)

A B C

1 4 1 100
2 2 106
3 3 112
4 4 118
5 5 121
6 X

I hope this is clear enough.

 

[Pete_UK]

Try this in A6:

=VLOOKUP(A1,A$2:B$5,2,0)

Note that a formula cannot "push" data to a cell, it can only "pull"
it from a cell. In this case it will pull the data from B4.

Your description is confusing - where does column C come into it?

Hope this helps.

Pete

 

[Max]

Try in A6: =OFFSET(INDIRECT(A1),,1)

If A1 contains the cell ref in text: B4 (not "+B4", btw)
then A6 will return what's in C4 (ie 1 col to the right)
which appears to be what you seek

 

[BEEJAY]

Pete:
1: As I understand it, look-ups column has to be in ascending order.
My lists are not, and they are not static either.
2: My mistake in question.
Line 8 should read: Read cell reference indicated in A1
(this is where I manually entered the B4 cell reference)
SO, A1 "shows" the Value of B4.
But, I'm trying to get the cell reference (B4) out of A1, in order to be
able to get the value of the cell one column to the left of B4, and enter
that in A6.
I hope this is clearer.

 

[Max]

As responded in the other branch,
Try in A6: =OFFSET(INDIRECT(A1),,1)
where A1 contains the cell ref: B4
(not "+B4", btw)

---

 

[BEEJAY]

Max:
I tried your sample but could not get it to return the value of C4.
(I changed A1 to Text).
Sorry that the sample came out so poorly.
I'm quite sure you understood my question properly, in spite of my entry
mistake and the poor chart provided, but, to be safe, let me try again.
Left most column is row numbers.

........A.....B..... C

1.....4..... 1......100
2............ 2......106
3.............3......112
4............ 4......118
5.............5......121
6.... X

I NEED the VALUE in A1, in this case, since I need "4", I enter +B4, in Cell
A1.
Then I'm looking to "read" the cell name referenced in A1, (which is B4),
and use that reference in my formula in A6.
So A6 would read something like OFFSET(INDIRECT(A1),,1)
Is this making any sense?
Or, assuming my explanation is better this time, is there another way to
accomplish this?

 

[Pete_UK]

The lookup column does not need to be in ascending order if you use
the fourth optional parameter set to FALSE (or 0).

Did you try to amend my formula to suit your latest example?

Put this in A6:

=VLOOKUP(A1,B$1:C$5,2,0)

Your example is misleading - I think you want to enter 4 in A1 to
indicate the row number, not the value that matches with A1 in column
B. If that is the case, then you could try this in A6:

=INDIRECT("C"&A1)

Hope this helps.

Pete

 

[Max]

Sorry for the miscommunication

.. (I changed A1 to Text).

I meant that you should simply enter in A1: B4
(don't enter the "+" sign)

It works ok here, just re-tested. Try it again
Place in A6: =OFFSET(INDIRECT(A1),,1)
Then enter into A1: B4
A6 should return what's in C4, ie: 118

---

 

[BEEJAY]

Greetings:
I've perhaps made this un-necessarily cumbersome.
The #'s shown in column B match the row #'s inadvertently. There is no
relation between the two.

Due to the complexity of the complete sheet, I'm very limited.
IF I use VLookUps, I would need to use more "IF" statements than the system
will allow.
I did get the OFFSET(INDIRECT) to work by inputing B4 as text.
However, I DO NEED, in Cell A1, the VALUE of B4, for calculation purposes.
In Cell A6 I need to "access" the Cell Reference used in A1.

In its simplest terms:
Cell A1: I Enter B4 - It Reads "4"
Cell A6: "Extract" the CELL REFERENCE in A1 to use in a formula in A6?

I hope I haven't muddied the waters further.