hi all, i'm looking for a formula that generates the value in the top row and left column of a table, based on the position of the input cell in that table. I've attached an example to make it a bit more clear what i'm looking for.
Thanks!
hi all, i'm looking for a formula that generates the value in the top row and left column of a table, based on the position of the input cell in that table. I've attached an example to make it a bit more clear what i'm looking for.
Thanks!
Q4=INDEX($B:$B,MAX(INDEX(($C$4:$L$18=$O4)*ROW($C$4:$L$18),0)))
R4=INDEX($3:$3,MAX(INDEX(($C$4:$L$18=$O4)*COLUMN($C$4:$L$18),0)))
Try this, copy & paste towards down
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Or
q4=sumproduct(($b$4:$b$18)*($c$4:$l$18=$o4))
r4=sumproduct(($c$3:$l$3)*($c$4:$l$18=$o4))
Use:
=INDEX($B$4:$B$18,SUM(INDEX(($C$4:$L$18=$O4)*ROW($C$4:$L$18),))-3)
and:
=INDEX($C$3:$L$3,SUM(INDEX(($C$4:$L$18=$O4)*COLUMN($C$4:$L$18),))-2)
for the first, and second, columns respectively.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh
max/index/row
max index column
works like a charm! thanks a million
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks