Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello -
I need help with the below IF statement. I am trying to use MATCH to look at multiple vales in a field.This is not working.
Is MATCH the correct function to use in this case? Can someone please suggest the right way to go about this.
Thank You.
*******************************************************************************************************
IF(Job='0','Mng Dir',
IF(Job='9' AND Level = '8', 'Mng Dir',
IF(Job='9' AND Level = '10', 'Dir',
IF(Job='9' AND Level = '11', 'Dir',
IF(Job='2' AND MATCH(Jobcode,'11111', '22222','33333',44444'), 'SrMgr',
IF(Job='2' AND NOT MATCH(Jobcode,'11111', '22222','33333',44444'), 'Dir',
'No')))))) as "NeedAgreement"
*******************************************************************************************************
Regards,
Archana
Hi Archana,
I think using Match() function there is a very smart option but Match() function returnna a number.
E.g. Match(vdOne, 'One', 'Two', 'Three') returns 1 as variable vdOne containe 'One' value. So in your case you should put:
IF(Job='0','Mng Dir',
IF(Job='9' AND Level = '8', 'Mng Dir',
IF(Job='9' AND Level = '10', 'Dir',
IF(Job='9' AND Level = '11', 'Dir',
IF(Job='2' AND MATCH(Jobcode,'11111', '22222','33333',44444') > 0, 'SrMgr',
IF(Job='2' AND MATCH(Jobcode,'11111', '22222','33333',44444') = 0, 'Dir',
'No')))))) as "NeedAgreement"
Hope it serves,
Regards,
H
Match returns 0 if no match and 1,2,3,4 in this case depending on which jobcode matched.
Try:
IF(Job='0','Mng Dir',
IF(Job='9' AND Level = '8', 'Mng Dir',
IF(Job='9' AND Level = '10', 'Dir',
IF(Job='9' AND Level = '11', 'Dir',
IF(Job='2' AND MATCH(Jobcode,'11111', '22222','33333',44444')>0, 'SrMgr',
IF(Job='2' AND MATCH(Jobcode,'11111', '22222','33333',44444')=0, 'Dir',
'No')))))) as "NeedAgreement"
Hi Archana,
I think using Match() function there is a very smart option but Match() function returnna a number.
E.g. Match(vdOne, 'One', 'Two', 'Three') returns 1 as variable vdOne containe 'One' value. So in your case you should put:
IF(Job='0','Mng Dir',
IF(Job='9' AND Level = '8', 'Mng Dir',
IF(Job='9' AND Level = '10', 'Dir',
IF(Job='9' AND Level = '11', 'Dir',
IF(Job='2' AND MATCH(Jobcode,'11111', '22222','33333',44444') > 0, 'SrMgr',
IF(Job='2' AND MATCH(Jobcode,'11111', '22222','33333',44444') = 0, 'Dir',
'No')))))) as "NeedAgreement"
Hope it serves,
Regards,
H
=IF(Job='0','Mng Dir',
IF(Job='9' AND Level = '8', 'Mng Dir',
IF(Job='9' AND Level = '10', 'Dir',
IF(Job='9' AND Level = '11', 'Dir',
IF(Job='2' AND MATCH(Jobcode,'11111', '22222','33333','44444' ) > 0, 'SrMgr',
IF(Job='2' and MATCH(Jobcode,'11111', '22222','33333','44444') = 0, 'Dir',
'No'))))))
Hector - This works.
Thank you Thank you Thank you!!
Thank You Cadir