Forum Discussion

Immortalis's avatar
Immortalis
Brass Contributor
Feb 10, 2018

IF Index Match combination.

I've been searching everywhere but can't find a solution to this in the right context.   I'm trying to nest an Index/Match array inside an IF statement with AND, like so...   =IF(AND(E35<>"",...
Formulas & Functions
Need Help
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Feb 10, 2018

Amir,

 

What's your goal is?

Please provide us with a sample of your data!

  • Immortalis's avatar
    Immortalis
    Brass Contributor
    Feb 10, 2018

    Thank you for the response, Haytham, 

     

    Providing a sample would be quite difficult since all the cells reference other cells and then add strings to them.

     

    What I'm trying to do is simply this…

     

    I’m working in cell G35, so…

     

    Check that cell E35 is NOT empty,

    and then

    Check to see that the contents of cell E35 doesn’t already exist within the range of cells F35 to F45 (using the Index/Match array)

     

    IF both are TRUE (the cell isn't empty and the data hasn't been used yet), then use cell the data from cell E35, in cell G35.

     

    If either checks are false, then simply put “n” into cell G35

    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Feb 10, 2018

      Thanks for this explanation!

      If so, then you have to use this formula instead of INDEX/MATCH:

      =IF(AND(E35<>"",ISNUMBER(MATCH(E35,F35:F45,0))),E35,"n")

       

      Hope that helps. 

      • Orlanie Lagrimas's avatar
        Orlanie Lagrimas
        Copper Contributor
        Nov 05, 2018

        Hi, Haytham, 

         

        Good Day!

         

        It seems that you're expert with excel.  I would like to ask a favor of you to help me in creating the formula below. 

         

        Test #No. of ItemsScorePercentage

        Orlanie L. Lagrimas
        DATE
        4-Nov-18Test I1149583%
        Test I605693%
        Test I605693%
        TEST II605287%
        TEST II605693%
        TEST IITimed outTimed out 
        TEST II605490%

         

        Above is the data that would like to sum-up using the table : 

         

        Weekly Average per TEST
        WEEKTEST #Average
        WE1110TEST I 
        TEST II 
        TEST III 
        TEST IV 
        TEST V 
        TEST VI 
        TEST VII 
        TEST VIII 
        TEST IX 
        TEST X 

         

        What formula can I out under average to sum up example for test 1 for the entire week? 

         

        Your response will be highly appreciated.  

         

        Thank you,

        Orlanie

Resources