Forum Discussion

Traugh23's avatar
Traugh23
Copper Contributor
Jul 27, 2022

Help With a Lookup / Reference Function

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.

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

    Traugh23 

    =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.

Resources