Forum Discussion
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<>"",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.
6 Replies
Amir,
What's your goal is?
Please provide us with a sample of your data!
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
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.
