Index Match Function with blank cell

[SAXON10]

Greeting,


I am apply the following formula, the formula return blank cells as a number instead of returning as blank. Is there a way to fix this, so that it returns just a blank value?

=IF(INDEX(DATA!$D$3:$D$25,MATCH(1,INDEX((DATA!$C$3:$C$25=$B3)*(MID(DATA!$B$3:$B$23,1,2)=MID($A3,2,2))*(DATA!$A$3:$A$25=C$2),0),0))="OK","OK",INDEX(DATA!$B$3:$B$25,MATCH(1,INDEX((DATA!$C$3:$C$25=$B3)*(MID(DATA!$B$3:$B$23,1,2)=MID($A3,2,2))*(DATA!$A$3:$A$25=C$2),0),0)))

<tbody>
</tbody>

https://www.dropbox.com/s/ef9kaipp0ucryyt/BLANKS.XLSX?dl=0

https://www.dropbox.com/s/snmjfvoxc3o9thb/MEE.PNG?dl=0

https://www.dropbox.com/s/auays7a10mb5j1c/MEEE.PNG?dl=0

 

[Marcelo Branco]

Maybe...

Formula in C3 of RESULT sheet, copied across and down
=IF(COUNTIFS(DATA!$C$3:$C$25,$B3,DATA!$A$3:$A$25,C$2,DATA!$D$3:$D$25,"OK"),"OK",IF(COUNTIFS(DATA!$C$3:$C$25,$B3,DATA!$A$3:$A$25,C$2,DATA!$D$3:$D$25,""),"",INDEX(DATA!$B$3:$B$25,MATCH(1,INDEX((DATA!$C$3:$C$25=$B3)*(DATA!$A$3:$A$25=C$2),0),0))))

Hope this helps

M.

 

[SAXON10]

Maybe...

Formula in C3 of RESULT sheet, copied across and down
=IF(COUNTIFS(DATA!$C$3:$C$25,$B3,DATA!$A$3:$A$25,C$2,DATA!$D$3:$D$25,"OK"),"OK",IF(COUNTIFS(DATA!$C$3:$C$25,$B3,DATA!$A$3:$A$25,C$2,DATA!$D$3:$D$25,""),"",INDEX(DATA!$B$3:$B$25,MATCH(1,INDEX((DATA!$C$3:$C$25=$B3)*(DATA!$A$3:$A$25=C$2),0),0))))

Hope this helps

M.

Thank for your help and formula working fine.

How to add and match the additional range the same formula

In data sheet Column B first two character "88" and its matched to "desired result sheet" in column A second two character

Please refer my previous formula mid function ((MID(DATA!$B$3:$B$23,1,2)=MID($A3,2,2))).

Please advice me.

 

[Marcelo Branco]

You can add this condition in the COUNTIFS
DATA!$B$3:$B$25,MID($A3,2,2)&"*"

M.

 

[SAXON10]

You can add this condition in the COUNTIFS
DATA!$B$3:$B$25,MID($A3,2,2)&"*"

M.

Thanks for the reply. I try to add the countifs but getting error "too few arguments". How to add the countifs the same formula.

Sorry for the inconvenience.

 

[Marcelo Branco]

C3 copied across and down

=IF(COUNTIFS(DATA!$B$3:$B$25,MID($A3,2,2)&"*",DATA!$C$3:$C$25,$B3,DATA!$A$3:$A$25,C$2,DATA!$D$3:$D$25,"OK"),"OK",IF(COUNTIFS(DATA!$B$3:$B$25,MID($A3,2,2)&"*",DATA!$C$3:$C$25,$B3,DATA!$A$3:$A$25,C$2,DATA!$D$3:$D$25,""),"",INDEX(DATA!$B$3:$B$25,MATCH(1,INDEX((DATA!$C$3:$C$25=$B3)*(DATA!$A$3:$A$25=C$2),0),0))))

M.