SOLVED

If within an If then another then another...(with a twist)

%3CLINGO-SUB%20id%3D%22lingo-sub-3009815%22%20slang%3D%22en-US%22%3EIf%20within%20an%20If%20then%20another%20then%20another...(with%20a%20twist)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3009815%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI've%20been%20trying%20to%20figure%20out%20the%20syntax%20for%20my%20formula%20but%20I%20can't.%20I%20need%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOkay%2C%20here%20is%20what%20I%20am%20trying%20to%20do%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF%20a%20value%20is%20find%20in%20a%20given%20range%20do%20another%20if(match)%20then%20if%20another%20value%20is%20find%20that%20range%20do%20another%20if(match)%20so%20on...%20But%20for%20each%20specific%20'if'%20if%20the%20value%20is%20not%20found%20then%20set%20the%20value%20of%20the%20cell%20%22sth%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(MATCH(Names!A2%2CS17%3AS20%2C0)%2CIF(MATCH(Names!A3%2CS17%3AS20)%2CIF(MATCH(Names!A4%2CS17%3AS20)%2CNames!A2%2CNames!A3%2CNames!A4%2CNames!A5)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I've%20been%20tinkering%20with%20but%20I%20can't%20get%20it%20to%20work.%20Where%20should%20I%20put%20the%20value%20if%20false%20for%20every%20if%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20if%20first%20one%20evaluates%20true%2C%20the%20true%20condition%20of%20that%20statement%20is%20another%20if%20statement.%20in%20that%20statement%20there%20is%20another%20if%20statement.%20for%20each%20if%20statement%20I%20need%20to%20set%20the%20value%20to%20sth%20if%20it%20evaluates%20false%20then%20stop%20there.%20I'm%20having%20trouble%20placing%20the%20false%20portion%20of%20each%20if.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAm%20I%20making%20any%20sense%3F%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3009815%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-3010106%22%20slang%3D%22en-US%22%3ERe%3A%20If%20within%20an%20If%20then%20another%20then%20another...(with%20a%20twist)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3010106%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1142876%22%20target%3D%22_blank%22%3E%40kheldar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20do%20you%20want%20to%20return%20if%20the%20third%20match%20is%20found%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hello,

I've been trying to figure out the syntax for my formula but I can't. I need your help.

 

Okay, here is what I am trying to do:

 

IF a value is find in a given range do another if(match) then if another value is find that range do another if(match) so on... But for each specific 'if' if the value is not found then set the value of the cell "sth".

 


=IF(MATCH(Names!A2,S17:S20,0),IF(MATCH(Names!A3,S17:S20),IF(MATCH(Names!A4,S17:S20),"sth","sth1","sth2")

 

This is what I've been tinkering with but I can't get it to work. Where should I put the value if false for every if?

 

So if first one evaluates true, the true condition of that statement is another if statement. in that statement there is another if statement. for each if statement I need to set the value to sth if it evaluates false then stop there. I'm having trouble placing the false portion of each if.

 

Am I making any sense?

3 Replies

@kheldar 

What do you want to return if the third match is found?

best response confirmed by kheldar (Contributor)
Solution

@kheldar 

As variant

=IF( ISNA( MATCH( Names!A2, S17:S20, 0 ) ),
     IF( ISNA( MATCH( Names!A3, S17:S20, 0 ) ),
         IF( ISNA( MATCH( Names!A4, S17:S20, 0 ) ),
             "not found",
              "sth2"
          ),
          "sth1"
      ),
     "sth" )
Sorry for not seeing your response, I thought I had mail notifications on.