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
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

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

    Thank you. I'm not fluent with Excel so I'm not familiar with ISNUMBER.

     

    Off the top, with your formula is appears the first part is right...

    IF(AND(E35<>""

    check to see that E35 is not empty. 

    The second part though,

    ISNUMBER(MATCH(E35,F35:F45,0)))

     looks as if it's looking for a match. I need it to be TRUE if there is no match.

     

    I did try it, and in the case of the cell NOT being empty and is NOT finding a match in the range, instead of entering the data from E35, it returned "n".

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

      I actually made it work using the following..

       

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

      Thank you SOOO much! I couldn't have done it without your help!! :) 

Resources