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!

Quote from bosco_yip

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

Display More

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

Appreciate the help Sir