Listing only non zero amounts

[daysleeper15]

I have a matrix in cells A1:E5 where row A lists the numbers 1, 2, 3, 4 and row 1 lists the letters A, B, C, D. Cells B3:E3 show the amounts 100, 0, 150, 0. Cells B5:E5 show the amounts 0, 0, 0, 25.

I am trying to make a formula in cell G1 and G2 that return the amounts 100 and 150 when I enter "2" in cell F1. Similarly if I were to enter "4" in cell F1 I would want to see 25 in G1 and G2 should be blank.

Is this possible?

[CAntosh]

Welcome to the forum!

If I'm understanding you correctly, the following formula, array entered (confirmed with Ctrl+Shift+Enter instead of Enter) in G1 and filled down should do the trick:

=IF(LARGE(($B$2:$E$5>0)*($A$2:$A$5=$F$1)*($B$2:$E$5),ROW(1:1))>0,LARGE(($B$2:$E$5>0)*($A$2:$A$5=$F$1)*($B$2:$E$5),ROW(1:1)),"")

Check out the attachment to see if I've got the arrangement correct:

[daysleeper15]

That's it. Thank you so much.