IF Index Match combination.

[Immortalis]

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<>"",E35<>(INDEX($F$35:$F$45,MATCH("*"&E35&"*",$F$35:$F$45,0)))),E35,"n")

It seems to work somewhat, however it returns a value of 0

I'd greatly appreciate any help with this.

Thank you in advance.

[#2]

Amir,

What's your goal is?

Please provide us with a sample of your data!

[#3]

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

[#4]

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. 

[#5]

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

[#6]

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!! :) 

[#7]

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-18	Test I	114	95	83%
Test I	60	56	93%
Test I	60	56	93%
TEST II	60	52	87%
TEST II	60	56	93%
TEST II	Timed out	Timed out
TEST II	60	54	90%

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

Weekly Average per TEST
WEEK	TEST #	Average
WE1110	TEST 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