Feb 01 2022 08:29 PM
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 {}
Updated formula with MATCH showing 0 in the "Stakeholder Name" Column
Source table referenced
Feb 01 2022 10:29 PM
SolutionHi @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
Feb 01 2022 10:43 PM
@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?
Feb 02 2022 09:42 AM
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.
Feb 02 2022 09:43 AM
Feb 02 2022 10:08 AM
Feb 02 2022 11:31 AM
Feb 01 2022 10:29 PM
SolutionHi @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