Ask Your Question

How to get row from ADDRESS().

asked 2018-11-18 15:56:38 +0100

Penguin Guru gravatar image

I created a new table and put the value "3" in A1 and "4" in A2.

What I would like to do is: =ROW(ADDRESS(MATCH(A1,A1:A2,0),1,4)) That produces "Err:504".

This works (result is "1"): =ROW(A1) This works (result is "A1"): =ADDRESS(MATCH(A1,A1:A2,0),1,4)

I have also tried: =CELL("ROW",ADDRESS(MATCH(A2,A2:A3,0),1,4)) That produces "#REF!"

Am I missing something obvious or is there another way to do this?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-11-18 19:26:55 +0100

JohnSUN gravatar image

Your method of calculation seems strange to me, but perhaps this is because you did not describe your task completely.

I think this formula will work for you: =ROW(INDIRECT(ADDRESS(MATCH(A1;A1:A2;0);1;4)))

edit flag offensive delete link more


Yes, this works perfectly. I'm not sure why this is necessary, but thank you! The actual function was pretty long and I had already simplified it when I was trying to figure this out.

Penguin Guru gravatar imagePenguin Guru ( 2018-11-19 01:18:40 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-11-18 15:56:38 +0100

Seen: 30 times

Last updated: Nov 18 '18