help with syntax/functions to select cells based on criteria of other cells...and not null

[ReneeBay5000]

Hello,

Folks helped me with a previous question about selecting cells based on criteria of other cells (see below). But now I've realized that I also need to exclude looking at cells that are blank/null but actually have the maximum depth. I'm tinkering with using IF/AND statements within the MAX statement without success so far. So, I'm hoping someone can help with syntax/other options. And example of what I mean and what I'm trying are in the screenshots below.

Thanks!

https://techcommunity.microsoft.com/t5/excel/help-with-syntax-functions-to-select-cells-based-on-cri...

[#2 · Accepted solution]

@ReneeBay5000 

=IFERROR(INDEX(B:B,LARGE(IF(($D:$D<>"")*($A:$A=$I2),ROW($A:$A)),1)),"")

You can try this formula which returns the expected results in my sheet. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. The formula was entered in cell J2 and copied across range J2:N4.

[#3]

That worked great! Thanks so much!