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 Joke
Why can't spreadsheets drive cars? They crash too often!

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,727
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
It's hard to work with pictures. Can you upload copies of your file?
 

CC268

Active Member
Joined
Mar 7, 2016
Messages
328
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.
 

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994
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
 

CC268

Active Member
Joined
Mar 7, 2016
Messages
328

ADVERTISEMENT

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.
 

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994
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'.
 

CC268

Active Member
Joined
Mar 7, 2016
Messages
328
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,303
Messages
5,836,526
Members
430,437
Latest member
Emilycr

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
Top