VLOOKUP of values COLUMN 1 and COLUMN 2

vas6566

New Member
Joined
Sep 24, 2019
Messages
5
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


A
B
C
D
E
DATE
NAME
CODE
BILL NO.
VALUE
01-04-2019
MINI
AAF
101
5000
02-04-2019
SONY
AAA
402
2000
03-04-2019
MINI
AAF
103
5500
04-04-2019
JOY
AFI
703
7000
05-04-2019
JOY
AFI
704
6000
06-04-2019
VIVA
AFO
115
5000

<tbody>
</tbody>

SHEET2


A
B
C
D
E
DATE
NAME
CODE
BILL NO.
VALUE
05-04-2019
JOY
AFI
704
6000
06-04-2019
VIVA
AFO
115
5000
03-04-2019
MINI
AAF
103
5500
04-04-2019
JOY
AFI
703
7000
01-04-2019
MINI
AAF
101
5000
02-04-2019
SONY
AAA
402
2000

<tbody>
</tbody>

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
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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
 

vas6566

New Member
Joined
Sep 24, 2019
Messages
5
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,332
Office Version
365
Platform
Windows
How about
=INDEX(Sheet2!$E$2:$E$7,MATCH(C2&"|"&D2,INDEX(Sheet2!$C$2:$C$7&"|"&Sheet2!$D$2:$D$7,0),0))
 

vas6566

New Member
Joined
Sep 24, 2019
Messages
5
Dear [FONT=&quot]Fluff ,
yea it worked , it worked, You have done it, Hats off dear Fluff. [/FONT]
:LOL::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]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,332
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

vas6566

New Member
Joined
Sep 24, 2019
Messages
5
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


ABCDE
DATENAMECODEBILL NO.VALUE
05-04-2019JOYAFI7046000
06-04-2019VIVAAFO1155000
03-04-2019MINIAAFG-1035500
04-04-2019JOYAFI703F7000
01-04-2019MINIAAF1015000
02-04-2019SONYAAA4022000

<tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,332
Office Version
365
Platform
Windows
How about
=INDEX(Sheet2!$E$2:$E$7,MATCH(C2&"|"&"*"&D2&"*",INDEX(Sheet2!$C$2:$C$7&"|"&Sheet2!$D$2:$D$7,0),0))
 

vas6566

New Member
Joined
Sep 24, 2019
Messages
5
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,332
Office Version
365
Platform
Windows
You're more than welcome & thanks for the feedback
 

Forum statistics

Threads
1,082,360
Messages
5,364,935
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top