RETRIEVING INFO FROM MOVING CELLS

[ottasight]

Hi, i'm trying to retrieve info from a cell that is related to a position of a different cell.

	a	b	c	d	e	f	g	h	i
1		1	2	3	4	5	6	7	8
2
3	s
4	1

when S is in A3, A4 contains 1. if i move S to E3, A1 still returns B1 instead of "5" from F1
formula for A4 is, if(a3="s",b1,""). if i move the s to E3 the formula automatically adjusts to, if(e3="s",b1,""). even though A3 went to E3, B1 did not go to F1.
Need help in retrieving f1 in cell A4, when the "S" moves to any different cell. thanks

 

[jtakw]

Hi,

Not entirely sure what your requirements are, your description is not clear for me...

May be this:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1	a	b	c	d	e	f	g	h	i
2		1	2	3	4	5	6	7	8
3
4					s
5	5
Sheet784

Cell Formulas
Range	Formula
A5	A5	=IFERROR(LOOKUP("zzz",A4:I4,B2:J2),"")

 

[jtakw]

Too late to edit.

Removed the erroneous header row to show correct cell/range references:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1		1	2	3	4	5	6	7	8
2
3					s
4	5
Sheet784

Cell Formulas
Range	Formula
A4	A4	=IFERROR(LOOKUP("zzz",A3:I3,B1:J1),"")

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Perhaps you might have other data in row 3 too? If so, one of these (depending on your version) might be appropriate.

21 02 21.xlsm
	A	B	C	D	E	F	G	H	I
1		1	2	3	4	5	6	7	8
2
3		h		s		a
4	4
5	4
s

Cell Formulas
Range	Formula
A4	A4	=XLOOKUP("s",A3:H3,B1:I1)
A5	A5	=INDEX(B1:I1,MATCH("s",A3:H3,0))

 

[jtakw]

And if OP's row 1 is strictly numbers, and row 3 value is not repeated, we may be able to use just SUMIF, but really need clarification from OP:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1		1	2	3	4	5	6	7	8
2
3		h		s		a
4	6
5	4
Sheet784

Cell Formulas
Range	Formula
A4	A4	=IFERROR(LOOKUP("zzz",A3:I3,B1:J1),"")
A5	A5	=SUMIF(A3:I3,"s",B1:J1)