Apply of Vlookup for comparison and entry feeding

For the compression of two datasheet lookup is more helpful for us. We are comparison the sheet and find out the extra data and missing data in both sheets (Example No – 1 ). It is also utilized to entering the value depends in the other value and make time-saving and error Free Entry. (example No - 2)

In Example No – 1 we are comparison Sheet No -1 to Sheet No -2 and find out the difference from sheet no – 2

Sheet No – 1

Sr. No	Name	Bank A/c No	Amount
1	Kamal	22658	5000
2	Rohit	65894	4000
3	Mohit	25689	3000
4	Sohan	45985	4500
5	Mak	45894	2500
6	Ganesh	45896	32500
		Total	51500

Sheet No - 2

Sr. No	Name	Bank A/c No	Amount
1	Ganesh	45896	32500
2	Kamal	22658	5000
3	Mak	45894	2500
4	Mohit	25689	8000
5	Rohit	65894	4000
		Total	52000

Apply the formula in sheet no -1

Before the apply the formula we consider the common and same value in the same sheet.

In the booth sheet bank, a/c no is the same so we are apply vlookup in bank a/c no

Sr. No	Name	Bank A/c No	Amount
1	Kamal	22658	5000	=VLOOKUP(C2,'sheet no -2 '!C:D,2,FALSE)
2	Rohit	65894	4000
3	Mohit	25689	3000
4	Sohan	45985	4500
5	Mak	45894	2500
6	Ganesh	45896	32500
		Total	51500

In lookup first, we are select common value so we are select c2 because bank a/c no is common, after that take comma and select sheet no -2 and select all C To D Columns  and in the time of selection no of select columns is show enter the no of the column we show the value and type false ( for Exact value match and true app value match ) after the enter, you show sheet no – 2 vale and copy the formula in all the column you show that value

Sr. No	Name	Bank A/c No	Amount
1	Kamal	22658	5000	5000
2	Rohit	65894	4000	4000
3	Mohit	25689	3000	8000
4	Sohan	45985	4500	#N/A
5	Mak	45894	2500	2500
6	Ganesh	45896	32500	32500
		Total	51500

It means that Sohan is not entered in sheet no – 2 because of that value shows that not applicable and Mohit take 8000 rs in sheet no -2 and sheet -1 shoe value of Rs 3000   

End of Example No – 1

Example No – 2

We have Data sheet of Emp.  In sheet -1

Emp No	Name	Father Name	Bank A/c No
105	Muktyar	Dinesh	22658
106	Rohit	ritesh	65894
107	Mohit	Hem bahadur	25689
108	Sohan	jin	45985
109	Mak	lokesh	45894
110	Ganesh	jitender	45896

We are main the bank file for amount distribution we are enter only Emp No and amount, another field is automatic shoe

We maintain file below format

Emp.No	Amount	Name	Bank  a/c No
105	5000	=vlookup(A2,'sheet1'!A:B,2,false)	=VLOOKUP(A2,'sheet1'!A:D,4,false)

After entering in the name show the name of Emp and Bank a/c No Copy or drag formula in the column you enter only Emp no and Amount name and bank a/c no automatic show, Like this

Emp.No	Amount	Name	Bank  a/c No
105	5000	Muktyar	22658
109	2500	Mak	45894
106	1254	Rohit	65894
107	1254	Mohit	25689
109	256	Mak	45894
		#N/A	#N/A
		#N/A	#N/A

In the above formula care that first one select common filed, second select sheet no -2 and common filed is the first field and select column we want the show, total no of columns between first and wanted columns and true or false.

For further query contact us

Kamal Kumar

Ph.9996480619