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

CC268

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

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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,589
It's hard to work with pictures. Can you upload copies of your file?
 

CC268

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

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
326
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,237
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top