Pick match with else condition

[ashis]

Hello,

I am trying to create a calculated dimension with pick and match with only exception else condition that means if there is no match then pick 'Not in Months' value. I am using the following syntax. However it is showing '-' instead of 'Not in Months' .

It is not picking up the else statement when there is no match. Is there any way to make it to work .

=Pick(Match(Bucket, '01', '12', '23', '34', '45', '56')+1,
'Not in Months','0-1 Month', '1-2 Month ', '2-3 Month', '3-4 Month ', '4-5 Month', '5-6 ')

[rajeshqvd]

Hi, Use below one

=Pick(1+Match(Bucket, '01', '12', '23', '34', '45', '56'),
'Not in Months','0-1 Month', '1-2 Month ', '2-3 Month', '3-4 Month ', '4-5 Month', '5-6 ')

[sunny_talwar · Accepted solution]

May be try this

=Pick(RangeSum(Match(Bucket, '01', '12', '23', '34', '45', '56'), 1),
'Not in Months','0-1 Month', '1-2 Month ', '2-3 Month', '3-4 Month ', '4-5 Month', '5-6 ')

[ashis]

Hi Sunny, Awesome it works. Could you please help me to understand how rangesum works here in this case with else statement.

[ashis]

Hi Rajesh,

Thank you for your reply, i tried that too but did not work out.

[sunny_talwar]

I think what was happening was that the Match was giving null so Null + 1 = Null... but rangesum handles it differently.... RangeSum(Null, 1) = 1

[ashis]

Thank you for clarification.