How to use Match in Nested IFs

[#1]

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

[craigsutton]

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"

[hector_munoz · Accepted solution]

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

[selcukcadir]

=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'))))))

[#5]

Hector - This works.

Thank you Thank you Thank you!!

[#6]

Thank You Cadir