AppNo -- is a defined name range from sheet 1
winner -- is a defined name range from sheet 2

=IF(ISERR(SMALL(IF(COUNTIF(winners,AppNo)=0,ROW(IN DIRECT("1:"&ROWS(AppNo)))),ROWS($1:1))),"",INDEX(A ppNo,SMALL(IF(COUNTIF(winners,AppNo)=0,ROW(INDIREC T("1:"&ROWS(AppNo)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed


"Montu" wrote:

I have some 20000 data for lottery in sheet 1 like :-
Sl No. Application No. Reg. No. Chque No. Name
01 17211 7552 215587 D.Gupta
02 14546 8258 112536 S.Das
Continued ..... to Sl no. 20000
I have some another data for whom has win lottery in sheet 2 like :-
Sl No. Application No. Reg. No. Chque No. Name
01 15311 1252 115327 M.Sharma
02 04836 4128 441562 S.Ghosh
Continued .... to Sl no. 1200.
Now I have to return the amount of the applicant who has not won the lottery.
Is there any formula to extract the application number who has not won the
lottery from sheet 1. help me