Formula to find a cell's contents based on another cell's address

[mlarson]

This is probably fairly simple but I’m not sure of the formula to use. Any help would be appreciated.

I have $D$4 showing in cell C1, which is good. I would like a formula for cell C2 and another for C3 with the following desired outputs…

I want the output in C2 to be 1 column to the right from the C1 address (which is $D$4). That would be cell E4, so I’d like the contents of E4 (“Lettuce”) to appear in C2.

I want the output in C3 to be 1 column to the right and 1 row down from the C1 address (which is $D$4). That would be cell E5, so I’d like the contents of E5 (“Tomato”) to appear in C3.

C1: $D$4
C2: (blank right now, needs formula)
C3: (blank right now, needs formula)
E4: Lettuce
E5: Tomato

Thank you!

 

[awoohaw]

The cells references in your request do not make sense. Could you post a mini workbook of what your expectations are, and which cells are calculated and which are data input. Here is what I think you are asking:

Mr Excel Questions 4.xlsm
	A	B	C	D	E
1			0
2			Lettuce
3			Tomato
4					Lettuce
5					Tomato
6
Sheet22

Cell Formulas
Range	Formula
C1	C1	=$D$4
C2:C3	C2	=E4

 

[ACommandLineKindaGuy]

If I'm getting you right, this is what you want

c2: =OFFSET(INDIRECT(C1),0,1)
c3: =OFFSET(INDIRECT(C1),1,1)

 

[mlarson]

My computer wasn't allowing me to download the mini-screen earlier, so I'm working on that issue.

In the meantime, here's the spreadsheet

Current

Desired Output

A

B

C

D

E

F

G

A

B

C

D

E

F

G

1​

$D$4

1​

$D$4

2​

(need formula)

2​

Lettuce

3​

(need formula)

3​

Tomato

4​

Lettuce

4​

Lettuce

5​

Tomato

5​

Tomato

6​

6​

7​

7​

 

[mlarson]

Note: the formula in C1 is not $D$4. That's what appears/shows in cell C1. The formula in C1 is a Cell Index Match that results in $D$4 showing. Apologies for the confusion.

 

[awoohaw]

can you post that index match formula please?

 

[Peter_SSs]

@mlarson
Did you try the formulas suggested by @ACommandLineKindaGuy ?
From what I understand, they do exactly what you asked. Here they are again in action.

23 02 12.xlsm
	C	D	E
1	$D$4
2	Lettuce
3	Tomato
4			Lettuce
5			Tomato
Formulas

Cell Formulas
Range	Formula
C2	C2	=OFFSET(INDIRECT(C1),0,1)
C3	C3	=OFFSET(INDIRECT(C1),1,1)

However, both INDIRECT & OFFSET are volatile functions so best avoided if possible. Posting your current C1 formula as requested above may allow a non-volatile option instead.

My computer wasn't allowing me to download the mini-screen earlier

Can you describe exactly what goes wrong and at what point in the process it goes wrong?

 

[mlarson]

That works, thank you! I might be interested in the less volatile option if there is one.

The formula in question is =CELL("address",INDEX($A$10:$A$173,MATCH("*"&D6&"*",$A$10:$A$173,0))) but that is from my actual worksheet (which is massive) and the data on it can't be shared. So, I used the tomato/lettuce sheet as an example.

If I'm getting you right, this is what you want

c2: =OFFSET(INDIRECT(C1),0,1)
c3: =OFFSET(INDIRECT(C1),1,1)

 

[Peter_SSs]

The formula in question is =CELL("address",INDEX($A$10:$A$173,MATCH("*"&D6&"*",$A$10:$A$173,0)))

That formula could never result in $D$4. However, if you wanted the values from the cell to the right (& one below that) for what that formula would return, then these two non-volatile formulas should give you those results without the need for OFFSET or INDIRECT or even that CELL("address" formula.

=INDEX($B$6:$B$173,MATCH("*"&D6&"*",$A$6:$A$173,0))

=INDEX($B$6:$B$173,MATCH("*"&D6&"*",$A$6:$A$173,0)+1)