Forum Discussion

Bruno171100's avatar
Bruno171100
Copper Contributor
Apr 11, 2023

Problem with function vlookup

Hi everyone!

In my excel i pretend search a material in a column B (with vlookup)  that contain some lines of components in column C and the suppose is select and copy those components that start with "GRAN" or "TRIT" or "CT". 

Can you sugest some resolution with code vba or Excel functions for this problem?

 

excel
Formulas and Functions
Macros and VBA

1 Reply

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 11, 2023

    Bruno171100 

    =INDEX(C$2:C$10,SMALL(IF(($B$2:$B$10=$B$14)*((LEFT($D$2:$D$10,4)=$A$14)+(LEFT($D$2:$D$10,4)=$A$15)+(LEFT($D$2:$D$10,2)=$A$16)),ROW($D$2:$D$10)-1),ROW($A1)))

    You can try this formula in the english version of e.g. Excel 2013. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell C14 and filled across range C14:E17.

    If you work with Office 365 or Excel 2021 you can apply the FILTER function.