VBA (Complex Vlookup) - Copy data from Sheet 1 to Sheet 2

CC268

Active Member
Joined
Mar 7, 2016
Messages
328
I have two Excel sheets populated with data. I'm trying to take the Part Number and Next Higher Part Number in Columns A and B of Sheet 1 and look for the same Part Number and Next Higher Part Number in Columns A and F of Sheet 2. If a match is found, then I want it to pull over data from Sheet 2 to Sheet 1 (e.g. pull the data in Column B in Sheet 2 to Column M in Sheet 1).

Kind of like a two variable Vlookup...

VQvqUVUHdFaebvJi6
VQvqUVUHdFaebvJi6
I can't get photos to post here, but here are links to examples of the two sheets.

https://photos.app.goo.gl/MCfwGXeSvfrJjzYS8

https://photos.app.goo.gl/1B37ZFwTyNzk3M6N9
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
It's hard to work with pictures. Can you upload copies of your file?
 
Upvote 0
It's hard to work with pictures. Can you upload copies of your file?

Understandable. I don't see a way to upload files here. I don't have access to file sharing websites as my employer does not allow them.
 
Upvote 0
You can do this with an index/match formula. Any reason it has to be VBA?

=INDEX(Sheet2!B:B,MATCH(1,(A2=Sheet2!A:A)*(B2=Sheet2!F:F),0))

this is an array formula, and must be entered with control + shift + enter
 
Upvote 0
You can do this with an index/match formula. Any reason it has to be VBA?

=INDEX(Sheet2!B:B,MATCH(1,(A2=Sheet2!A:A)*(B2=Sheet2!F:F),0))

this is an array formula, and must be entered with control + shift + enter

It doesn't have to be VBA. I tried your above recommendation and I got a #N/A out of it....trying to play around with it a bit.
 
Upvote 0
It doesn't have to be VBA. I tried your above recommendation and I got a #N/A out of it....trying to play around with it a bit.


Might be the columns.

You said ABOVE A & B on Sheet1 have to match A & F on Sheet2 then return data from column B on Sheet2.

However on your screenshots it was A & E on Sheet2 not A & F.

Also when you put the formula in make sure you press 'ctrl + shift + enter' and dont just press 'ENTER'.
 
Upvote 0
Might be the columns.

You said ABOVE A & B on Sheet1 have to match A & F on Sheet2 then return data from column B on Sheet2.

However on your screenshots it was A & E on Sheet2 not A & F.


Also when you put the formula in make sure you press 'ctrl + shift + enter' and dont just press 'ENTER'.

I ended up just concatenating the two columns together so I could treat it as one cell and then did a Vlookup. Not the most elegant solution, but it works.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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