Forum Discussion

ReneeBay5000's avatar
ReneeBay5000
Copper Contributor
Jul 27, 2022
Solved

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

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-criteria-of/m-p/3466769#M149227

 

 

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Jul 27, 2022

    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.

     

2 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jul 27, 2022

    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.

     

Resources