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.
max/index/row
max index column
works like a charm! thanks a million
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks