Plot if blank non blank and given text matches

Hello Friends

Please find the attached file (Plot if blank non blank and given text matches.xlsx).

The Column B should be blank and Columns A, F and I should be non blank and Column D shoud matches with the given text in the Cells K3:K8.

If the blank non blank and given text matches then need to plot in the light yellow cells.

thanks in advance.

Try

M3=IF(AND(INDEX($B$2:$B$15,MATCH($L3,$A$2:$A$15,0))="",INDEX($A$2:$A$15,MATCH($L3,$A$2:$A$15,0))<>"",INDEX($F$2:$F$15,MATCH($L3,$A$2:$A$15,0))<>"",INDEX($H$2:$H$15,MATCH($L3,$A$2:$A$15,0))<>"",MATCH($J$3:$J$8,$D$2:$D$15,0)),INDEX($A$2:$H$15,MATCH($L3,$A$2:$A$15,0),(COLUMNS($Q$1:Q1)-1)*2+4),5) and drag towards the cell.

m3

=IFERROR(INDEX(A:A,SMALL(INDEX(((COUNTIF($K$3:$K$8,$D$2:$D$15)=0)+($A$2:$A$15="")+($F$2:$F$15="")+($B$2:$B$15<>""))*10^10+ROW($A$2:$A$15),0),ROWS(L$3:L3))),"")

n3

=IFERROR(INDEX(D:D,SMALL(INDEX(((COUNTIF($K$3:$K$8,$D$2:$D$15)=0)+($A$2:$A$15="")+($F$2:$F$15="")+($B$2:$B$15<>""))*10^10+ROW($A$2:$A$15),0),ROWS(M$3:M3))),"")

o3

=IFERROR(INDEX(F:F,SMALL(INDEX(((COUNTIF($K$3:$K$8,$D$2:$D$15)=0)+($A$2:$A$15="")+($F$2:$F$15="")+($B$2:$B$15<>""))*10^10+ROW($A$2:$A$15),0),ROWS(N$3:N3))),"")

p3

=IFERROR(INDEX(I:I,SMALL(INDEX(((COUNTIF($K$3:$K$8,$D$2:$D$15)=0)+($A$2:$A$15="")+($F$2:$F$15="")+($B$2:$B$15<>""))*10^10+ROW($A$2:$A$15),0),ROWS(O$3:O3))),"")

try this and copy towards down

Hello Ankur

Thanks for your formula.

But it shows #N/A, Also it takes the reference from the unreleated blank cells i.e., Q1, L3 and J:J8, can you upload a file with solution formula.

Hello Siva and John

Thanks to both of you

Your formulas working well

thanks again