Return the Cell address based on a value

[ds_robb]

Hi,

Can you advise how I can check a cell address based on a number contained within it. So what I have (Let's say in A1 going across to J1) are the numbers 1 through 10, so A1 = 1, B1=2, C1=3 etc up to 10. On the line below, all the cells are blank, apart from one which will contain a "1". I need a formula that would show me which cell the 1 resides in so I can say OK that's in cell C2 (under C1) which I know is a 3. Therefore the item on this line is at stage 3 of a lifecycle. I hope that makes sense.

 

[DanteAmor]

How about:

varios 09abr2020.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	1	2	3	4	5	6	7	8	9	10
2			1								3
Hoja2

Cell Formulas
Range	Formula
K2	K2	=INDEX(A1:J1,0,MATCH(1,A2:J2,0))

 

[ds_robb]

Many thanks, that's exactly what I needed.

 

[Peter_SSs]

On the line below, all the cells are blank, apart from one which will contain a "1".

If that is the case then you could also use this column K formula.
And if the headers really are 1 to 10 then you could just use column L formula.

20 04 10.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4	5	6	7	8	9	10
2			1								3	3
3						1					6	6
ds_robb

Cell Formulas
Range	Formula
K2:K3	K2	=LOOKUP(1,A2:J2,A$1:J$1)
L2:L3	L2	=MATCH(1,A2:J2)

 

[DanteAmor]

Many thanks, that's exactly what I needed.

Glad we could help & thanks for the feedback

 

[ds_robb]

Can I add one more thing to this So assuming column A now has an ID Code. But the codes are not sequential, I want to be able to first look up where ID 3 sits and then find where on that particular row the number 1 sits. So I presume I need a version of VLOOKUP which first searches for the ID 3 and then the match to find the column on that row with a 1 in. I'm trying to compare two tables to show where the status (1-10) has moved week to week, but the issue is the ID's are not always sequential, so row 3 in the spreadsheet example below week 1 has a ID 3..... week 2 could be a different ID in here and 3 is sitting elsewhere, say row 5.... so I need to locate it to compare it.

I hope that makes sense

ID	1	2	3	4	5	6	7	8	9	10
2			1
3						1

 

[Peter_SSs]

Try either of these

20 04 10.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ID	1	2	3	4	5	6	7	8	9	10		ID	Hdr	Hdr
2	2			1									3	5	5
3	4						1						2	3	3
4	3					1
5	8		1
ds_robb (2)

Cell Formulas
Range	Formula
N2:N3	N2	=LOOKUP(1,INDEX(B$2:K$5,MATCH(M2,A$2:A$5,0),0),B$1:K$1)
O2:O3	O2	=INDEX(B$1:K$1,MATCH(1,INDEX(B$2:K$5,MATCH(M2,A$2:A$5,0),0)))

 

[ds_robb]

Try either of these

20 04 10.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ID	1	2	3	4	5	6	7	8	9	10		ID	Hdr	Hdr
2	2			1									3	5	5
3	4						1						2	3	3
4	3					1
5	8		1
ds_robb (2)

Cell Formulas
Range	Formula
N2:N3	N2	=LOOKUP(1,INDEX(B$2:K$5,MATCH(M2,A$2:A$5,0),0),B$1:K$1)
O2:O3	O2	=INDEX(B$1:K$1,MATCH(1,INDEX(B$2:K$5,MATCH(M2,A$2:A$5,0),0)))

Thank you Peter, that worked perfectly, much appreciated.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.