Return value sheet using index and match formula

0
votes

I am trying to make a return value sheet using index and match formula. it's working when I showed a specific cell (check my first formula) 

=INDEX($C$1:$C$7,MATCH(0,IF(($E$2=$A$1:$A$7)*($F$2=$B$1:$B$7),COUNTIF($G$1:$G2,$C$1:$C$7),""),0))

but when I try to use find a text value in this selected cell it's not working (Check my second formula).

 =INDEX($C$1:$C$7,MATCH(0,IF(($E$2=$A$1:$A$7)*("*C*"=$B$1:$B$7),COUNTIF($G$1:$G1,$C$1:$C$7),""),0))

So please advise us if you have any solution of this formula or any other option to show same results

SPREAD SHEET.

1
excel-formula

1 Answers

0
votes

You will need to use SEARCH or FIND:

 =INDEX($C$1:$C$7,MATCH(0,IF(($E$2=$A$1:$A$7)*(ISNUMBER(SEARCH("C",$B$1:$B$7))),COUNTIF($G$1:$G1,$C$1:$C$7),""),0))