Hi you have shared very useful excel formula which was very helpful to me. I have some issues in applying VLOOKUP. If possible pls help to resolve the following;
I have 2 separate excel sheets both have same data in multiple columns but not in the same sequence. I paste the sheet 1 below
[TABLE="width: 342"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]DATE
[/TD]
[TD]NAME
[/TD]
[TD]CODE
[/TD]
[TD]BILL NO.
[/TD]
[TD]VALUE
[/TD]
[/TR]
[TR]
[TD]01-04-2019
[/TD]
[TD]MINI
[/TD]
[TD]AAF
[/TD]
[TD]101
[/TD]
[TD]5000
[/TD]
[/TR]
[TR]
[TD]02-04-2019
[/TD]
[TD]SONY
[/TD]
[TD]AAA
[/TD]
[TD]402
[/TD]
[TD]2000
[/TD]
[/TR]
[TR]
[TD]03-04-2019
[/TD]
[TD]MINI
[/TD]
[TD]AAF
[/TD]
[TD]103
[/TD]
[TD]5500
[/TD]
[/TR]
[TR]
[TD]04-04-2019
[/TD]
[TD]JOY
[/TD]
[TD]AFI
[/TD]
[TD]703
[/TD]
[TD]7000
[/TD]
[/TR]
[TR]
[TD]05-04-2019
[/TD]
[TD]JOY
[/TD]
[TD]AFI
[/TD]
[TD]704
[/TD]
[TD]6000
[/TD]
[/TR]
[TR]
[TD]06-04-2019
[/TD]
[TD]VIVA
[/TD]
[TD]AFO
[/TD]
[TD]115
[/TD]
[TD]5000
[/TD]
[/TR]
</tbody>[/TABLE]
SHEET2
[TABLE="width: 340"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]DATE
[/TD]
[TD]NAME
[/TD]
[TD]CODE
[/TD]
[TD]BILL NO.
[/TD]
[TD]VALUE
[/TD]
[/TR]
[TR]
[TD]05-04-2019
[/TD]
[TD]JOY
[/TD]
[TD]AFI
[/TD]
[TD]704
[/TD]
[TD]6000
[/TD]
[/TR]
[TR]
[TD]06-04-2019
[/TD]
[TD]VIVA
[/TD]
[TD]AFO
[/TD]
[TD]115
[/TD]
[TD]5000
[/TD]
[/TR]
[TR]
[TD]03-04-2019
[/TD]
[TD]MINI
[/TD]
[TD]AAF
[/TD]
[TD]103
[/TD]
[TD]5500
[/TD]
[/TR]
[TR]
[TD]04-04-2019
[/TD]
[TD]JOY
[/TD]
[TD]AFI
[/TD]
[TD]703
[/TD]
[TD]7000
[/TD]
[/TR]
[TR]
[TD]01-04-2019
[/TD]
[TD]MINI
[/TD]
[TD]AAF
[/TD]
[TD]101
[/TD]
[TD]5000
[/TD]
[/TR]
[TR]
[TD]02-04-2019
[/TD]
[TD]SONY
[/TD]
[TD]AAA
[/TD]
[TD]402
[/TD]
[TD]2000
[/TD]
[/TR]
</tbody>[/TABLE]
Sheet1 was prepared by one person and Sheet2 was generated by another person. it has got thousands of rows.
I have to match CODE and BILL NO. in the rows of Sheet1 with the CODE and BILL NO. in sheet2. In simple I have to get the VALUE of sheet2 against the same CODE AND same BILL NO. in sheet1. This will ensure that value against each BILL NO. is same in both the sheets ( sheet1 and sheet2) kindly share suitable excel formula.
thanks in advance
I have 2 separate excel sheets both have same data in multiple columns but not in the same sequence. I paste the sheet 1 below
[TABLE="width: 342"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]DATE
[/TD]
[TD]NAME
[/TD]
[TD]CODE
[/TD]
[TD]BILL NO.
[/TD]
[TD]VALUE
[/TD]
[/TR]
[TR]
[TD]01-04-2019
[/TD]
[TD]MINI
[/TD]
[TD]AAF
[/TD]
[TD]101
[/TD]
[TD]5000
[/TD]
[/TR]
[TR]
[TD]02-04-2019
[/TD]
[TD]SONY
[/TD]
[TD]AAA
[/TD]
[TD]402
[/TD]
[TD]2000
[/TD]
[/TR]
[TR]
[TD]03-04-2019
[/TD]
[TD]MINI
[/TD]
[TD]AAF
[/TD]
[TD]103
[/TD]
[TD]5500
[/TD]
[/TR]
[TR]
[TD]04-04-2019
[/TD]
[TD]JOY
[/TD]
[TD]AFI
[/TD]
[TD]703
[/TD]
[TD]7000
[/TD]
[/TR]
[TR]
[TD]05-04-2019
[/TD]
[TD]JOY
[/TD]
[TD]AFI
[/TD]
[TD]704
[/TD]
[TD]6000
[/TD]
[/TR]
[TR]
[TD]06-04-2019
[/TD]
[TD]VIVA
[/TD]
[TD]AFO
[/TD]
[TD]115
[/TD]
[TD]5000
[/TD]
[/TR]
</tbody>[/TABLE]
SHEET2
[TABLE="width: 340"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]DATE
[/TD]
[TD]NAME
[/TD]
[TD]CODE
[/TD]
[TD]BILL NO.
[/TD]
[TD]VALUE
[/TD]
[/TR]
[TR]
[TD]05-04-2019
[/TD]
[TD]JOY
[/TD]
[TD]AFI
[/TD]
[TD]704
[/TD]
[TD]6000
[/TD]
[/TR]
[TR]
[TD]06-04-2019
[/TD]
[TD]VIVA
[/TD]
[TD]AFO
[/TD]
[TD]115
[/TD]
[TD]5000
[/TD]
[/TR]
[TR]
[TD]03-04-2019
[/TD]
[TD]MINI
[/TD]
[TD]AAF
[/TD]
[TD]103
[/TD]
[TD]5500
[/TD]
[/TR]
[TR]
[TD]04-04-2019
[/TD]
[TD]JOY
[/TD]
[TD]AFI
[/TD]
[TD]703
[/TD]
[TD]7000
[/TD]
[/TR]
[TR]
[TD]01-04-2019
[/TD]
[TD]MINI
[/TD]
[TD]AAF
[/TD]
[TD]101
[/TD]
[TD]5000
[/TD]
[/TR]
[TR]
[TD]02-04-2019
[/TD]
[TD]SONY
[/TD]
[TD]AAA
[/TD]
[TD]402
[/TD]
[TD]2000
[/TD]
[/TR]
</tbody>[/TABLE]
Sheet1 was prepared by one person and Sheet2 was generated by another person. it has got thousands of rows.
I have to match CODE and BILL NO. in the rows of Sheet1 with the CODE and BILL NO. in sheet2. In simple I have to get the VALUE of sheet2 against the same CODE AND same BILL NO. in sheet1. This will ensure that value against each BILL NO. is same in both the sheets ( sheet1 and sheet2) kindly share suitable excel formula.
thanks in advance