Vlookup or array formula based on criteria

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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!

  • In "Map" sheet F2, formula copied down :


    =IF($G2="Y",IFERROR(VLOOKUP($B2,Lookup!$C$2:$G$52,MATCH(C2,Lookup!$C$1:$G$1,0),0),""),"")


    However,


    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


    Regards

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


    Then,


    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))),"")


    and,


    Trailing space in B12 should be removed


    Regards

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


    Appreciate the help Sir:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!