SOLVED

Filter Function Only Showing Chosen Columns using Match

%3CLINGO-SUB%20id%3D%22lingo-sub-3102717%22%20slang%3D%22en-US%22%3EFilter%20Function%20Only%20Showing%20Chosen%20Columns%20using%20Match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3102717%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20replace%20the%20column%20numbers%20in%20the%20%7B%7D%20with%20a%20match%20function%20referencing%20the%20headers%20from%20the%20source%20table%20but%20the%20formula%20is%20spitting%20out%200.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInitial%20formula%20with%20%7B%7D%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ligg_1_0-1643775775111.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344421i751E7DFD0C855E2D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ligg_1_0-1643775775111.png%22%20alt%3D%22ligg_1_0-1643775775111.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUpdated%20formula%20with%20MATCH%20showing%200%20in%20the%20%22Stakeholder%20Name%22%20Column%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ligg_1_1-1643775931185.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344422i240A27CAC4BC371B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ligg_1_1-1643775931185.png%22%20alt%3D%22ligg_1_1-1643775931185.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESource%20table%20referenced%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ligg_1_2-1643776090659.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344423i62293C79AFF21444%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ligg_1_2-1643776090659.png%22%20alt%3D%22ligg_1_2-1643776090659.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3102717%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3103119%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20Function%20Only%20Showing%20Chosen%20Columns%20using%20Match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103119%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1292858%22%20target%3D%22_blank%22%3E%40ligg_1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20try%20any%20of%20the%20following%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DMATCH(N8%3AQ8%2CStakeholderCommPlan%5B%23Headers%5D%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eor%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DXMATCH(N8%3AQ8%2CStakeholderCommPlan%5B%23Headers%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fmatch-function-e8dffd45-c762-47d6-bf89-533f4a37673a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EMATCH%3C%2FA%3E%3A%20default%20value%20for%203rd%20optional%20arg.%20(%3CSPAN%3Ematch_type)%20is%201%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fxmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EXMATCH%3C%2FA%3E%3A%26nbsp%3Bdefault%20value%20for%203rd%20optional%20arg.%20(match_mode)%20is%200%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20none%20of%20the%20above%20fix%20the%20issue%20please%20attach%20a%20sample%20workbook%20to%20your%20next%20reply%20-%20Thanks%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3103137%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20Function%20Only%20Showing%20Chosen%20Columns%20using%20Match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1292858%22%20target%3D%22_blank%22%3E%40ligg_1%3C%2FA%3E%26nbsp%3BReplicated%20your%20model%20in%20something%20similar%20and%20the%20formulae%20should%20work.%20The%20only%20time%20I%20can%20get%20a%20zero%20in%20the%20first%20column%20but%20the%20others%20filled%20correctly%20is%20when%20the%20column%20name%20is%20not%20the%20same.%20So%2C%20are%20you%20sure%20that%20the%20%22Stakeholder%20Name%22%20is%20exactly%20the%20same%20in%20both%20places%3F%20No%20trailing%20spaces%20in%20one%20of%20them%20by%20any%20chance%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3106574%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20Function%20Only%20Showing%20Chosen%20Columns%20using%20Match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3106574%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%20The%20first%20option%20worked!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20know%20if%20it%20is%20possible%20to%20use%20the%20SORTBY%20function%20here%20to%20sort%20by%20%22next%20communication%20date%22%20and%20then%20%22next%20communication%20responsible%20party%22%20(or%20at%20a%20minimum%20%22next%20communication%20date%22)%3F%20I%20am%20not%20able%20to%20figure%20this%20out.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ligg_1_0-1643823718837.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344687i0D7BC0965A312FF3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ligg_1_0-1643823718837.png%22%20alt%3D%22ligg_1_0-1643823718837.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello - 

 

I am trying to replace the column numbers in the {} with a match function referencing the headers from the source table but the formula is spitting out 0. 

 

Initial formula with {} 

ligg_1_0-1643775775111.png

 

Updated formula with MATCH showing 0 in the "Stakeholder Name" Column 

ligg_1_1-1643775931185.png

 

Source table referenced 

ligg_1_2-1643776090659.png

 

 

 

 

6 Replies
best response confirmed by ligg_1 (Occasional Contributor)
Solution

Hi @ligg_1 

 

Could you try any of the following:

=MATCH(N8:Q8,StakeholderCommPlan[#Headers],0)

or

=XMATCH(N8:Q8,StakeholderCommPlan[#Headers])

 

MATCH: default value for 3rd optional arg. (match_type) is 1

XMATCH: default value for 3rd optional arg. (match_mode) is 0

 

If none of the above fix the issue please attach a sample workbook to your next reply - Thanks

@ligg_1 Replicated your model in something similar and the formulae should work. The only time I can get a zero in the first column but the others filled correctly is when the column name is not the same. So, are you sure that the "Stakeholder Name" is exactly the same in both places? No trailing spaces in one of them by any chance?

 

@L z. 

Thanks! The first option worked!

 

Do you know if it is possible to use the SORTBY function here to sort by "next communication date" and then "next communication responsible party" (or at a minimum "next communication date")? I am not able to figure this out.

ligg_1_0-1643823718837.png

 

I copy and pasted the cell to ensure nothing was different and it still was pushing out "0". The solution by L z. worked when I added "0" to the match function.

Thanks for your help!
I'll (or someone else) be glad to have a look to your SORTBY follow up question but please do 2 things if you don't mind:
- Open a separate discussion
- Attach your workbook (remove sensitive data if any) to this new discussion as it very very difficult to work with pictures only given the length of your formula
Thanks

@L z. thank you! I created a new forum here. I connected the document via Google drive because I don't think I have access to upload files directly. 

 

Thanks!