Forum Discussion

davids4500's avatar
davids4500
Copper Contributor
Jan 24, 2024

Excel Formulas and functions Vlookup for partial search and multiple conditions and OR condition

I have a excel spreadsheet and i need to lookup all itemcodes on worksheet "ChreosdataA", that have either "AFR" or "CCH" in column A:A, and have the words "Air Freshener" in column C. and display them in the worksheet "Corestock" in column A with no blank rows between them. Also the words are only partial matches as all codes are unique except for the prefix. I hope you can help please. David

excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Jan 24, 2024
    Attach a sample excel workbook and then show your desired output.
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 24, 2024

    davids4500 

     

    =FILTER(ChreosdataA!A2:A1000, (ISNUMBER(SEARCH("AFR", ChreosdataA!A2:A1000))+ISNUMBER(SEARCH("CCH", ChreosdataA!A2:A1000)))*ISNUMBER(SEARCH("Air Freshener", ChreosdataA!C2:C1000)), "")

     

    If you have more than 1000 rows of data, expand the ranges in the formula

    • davids4500's avatar
      davids4500
      Copper Contributor
      Sep 30, 2024

      HansVogelaar 

      HI

      I need to remove the crossed out section, what modification to the formula do i need to make?

      =FILTER(ChreosdataA!A2:A1000, (ISNUMBER(SEARCH("AFR", ChreosdataA!A2:A1000))+ISNUMBER(SEARCH("CCH", ChreosdataA!A2:A1000)))*ISNUMBER(SEARCH("Air Freshener", ChreosdataA!C2:C1000)), "")

      Thank you david

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 01, 2024

        davids4500 

        This should do it:

        =FILTER(ChreosdataA!A2:A1000, ISNUMBER(SEARCH("AFR", ChreosdataA!A2:A1000))*ISNUMBER(SEARCH("Air Freshener", ChreosdataA!C2:C1000)), "")