Vlookup or array formula based on criteria

  • Hi Folks -

    Struggling to find out how to make this work so wanted to reach out.

    On the attached spreadsheet I'm trying to return Column F (Rep Firm) based on some criteria. What I would like to happen is for an equation to look at Column G. If Column G has a:

    • "N" - then nothing needs to happen
    • "Y" - then look at what is in column C (Channel) and do a lookup on the 2nd tab (Lookup) and display the right firm based on that state (Column B) in that row

    For Example for Row 2 -

    • Has a "Y" in Column G
    • Column C reflects it is "HVAC"
    • Column B reflect State is KY
    • Lookup value in Column F should reflect "Miller Components"

    Thanks in advance for the help!


    • Sample.xlsx

      (21.31 kB, downloaded 76 times, last: )
  • In "Map" sheet F2, formula copied down :



    1] Remove trailing space in B12

    2] You have multiple value in cells B99, B243 and B248 of which the above formula will return blank


  • Further to my posted formula in Post #.2,

    Should you wanted to return multiple result in respect of the multiple criteria, you need TEXTJOIN function of which available in Office 365 or above.


    In "Map" sheet F2, array formula (confirm pressing Ctrl+Shift+Enter instead of just Enter) copied down :

    =IF($G2="Y",TEXTJOIN(", ",1,INDEX(Lookup!$D$2:$G$52,N(IF(1,MATCH(FILTERXML("<a><b>"&SUBSTITUTE(B2,",","</b><b>")&"</b></a>","//b"),Lookup!$C$2:$C$56,0))),MATCH(C2,Lookup!$D$1:$G$1,0))),"")


    Trailing space in B12 should be removed


  • Thanks Bosco_yip. Unfortunately I only have Office Pro Plus 2016, but your first formula worked wonders for me.

    Appreciate the help Sir:)