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

Take a look at this link:
http://www.contextures.com/xlFunctions02.html

That should give you what you need. When you have time, look at other
sections of the sit; it is a FANTASTIC resource.

Regards,
Ryan--

--
RyGuy


"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

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