VLOOKUP of values COLUMN 1 and COLUMN 2

vas6566

New Member
Joined
Sep 24, 2019
Messages
20
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Try

in Sheet1!F2
=LOOKUP(2,1/(Sheet2!C$2:C$100000=C2)*(Sheet2!D$2:D$100000),(Sheet2!E$2:E$100000))
and copy down Sheet1 column F
 
Upvote 0
Hi Special-K99,
Thanks for your valuable effort and reply. I copied the formula in Sheet1!F2, it shows #N/A. I think you have tried maximum. Can you pls try the same in copying the data in sheet1 and sheet2 and apply the formula in Sheet 1, F2. If you get better result, kindly share with me. Thanks a lot for extending helping hand to someone unknown.
 
Upvote 0
How about
=INDEX(Sheet2!$E$2:$E$7,MATCH(C2&"|"&D2,INDEX(Sheet2!$C$2:$C$7&"|"&Sheet2!$D$2:$D$7,0),0))
 
Upvote 0
Dear [FONT=&quot]Fluff ,
yea it worked , it worked, You have done it, Hats off dear Fluff. [/FONT]
:laugh::pray:[FONT=&quot] I find no words to thank you, Rather I never used such formula so far. I was really struggling until i got your formula. Mere a word of "thank you" wont be sufficient at this moment. Thanks in million. please be helpful to others like this, heavenly graces be with you. [/FONT]
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
Dear Fluff,

is it possible to apply wildcard in the column where BILL No. is listed. Because in Sheet 2 some bills nos are with hyphen, some bills are prefixed with alphabets.

for example pls refer the bills column mentioned below. thanks in advance
SHEET2


[TABLE="class: cms_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]G-103[/TD]
[TD]5500[/TD]
[/TR]
[TR]
[TD]04-04-2019[/TD]
[TD]JOY[/TD]
[TD]AFI[/TD]
[TD]703F[/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]
 
Upvote 0
How about
=INDEX(Sheet2!$E$2:$E$7,MATCH(C2&"|"&"*"&D2&"*",INDEX(Sheet2!$C$2:$C$7&"|"&Sheet2!$D$2:$D$7,0),0))
 
Upvote 0
Dear Fluff,

:pray::pray::pray::). i got it completely this time. I dont know how to thank you, you are helping some one from far away. I applied the formula to almost 9800 rows, everything worked fine. I am able to identify the rows with mismatch easily. Of course your help has given big help not only for my ongoing work, but also to the one i may face in future. You must be having big heart to respond quickly with alternative solution. you responded very quickly , but i , as a recipient of help,could not send thanking message to you at the same speed due to my work. Thanks fluff, once again no words. Hope you are a valuable member to this group. see you
 
Upvote 0
You're more than welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top