I have data 2,166 rows long. I want to lookup column L for CCEX only and return column A's values with no spaces/blanks in-between. Hence adding a skip over command for those values not meeting CCEX. Could someone help me with this function. Thank you.
=IFERROR(INDEX($A$2:$A$2166,SMALL(IF($L$2:$L$2166="D",ROW($A$2:$A$2166)-1),ROW(1:1))),"")You can try this formula with the data layout of the example. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
If you work with Office365 or 2021 you can use the FILTER function for this.