We help IT Professionals succeed at work.

double vlookup?

swjtx99
swjtx99 asked
on
152 Views
Last Modified: 2014-12-31
Hi,

Looking for a formula that will match the contents of two columns instead of just one as in a standard vlookup

Example:
Sheet1 has A,B,C
Sheet2 has A,B

I want to pull C from Sheet1 into C of Sheet2 for every row where both A and B match on both sheets.

Thanks in advance,

swjtx99
Comment
Watch Question

SimonPrincipal Analyst
CERTIFIED EXPERT

Commented:
Use an intermediate formula (=A1& "_"&B1) on both sheets to concatenate columns A&B and then use that as the key for the vlookup
CERTIFIED EXPERT

Commented:
Try this eexample below.

Just adjust based on your excel file.

=VLOOKUP(A1&B1,$D1:$F26,3,0)

Open in new window

Microsoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi ProfessorJimJam,

I think your formula is what I need but having a little trouble. Everytime I hit Cntrl Shift Enter, it opens a file explorer box with a heading of "Update Values: PL" and I hit cancel then another opens with the header "Update Values: List" I hit cancel again but not getting the results expected.

Thanks,

swjtx99

Author

Commented:
Attached is an example sheet.Example.xlsx

Author

Commented:
Sorry, that was the wrong example sheet.
Example.xlsx

Author

Commented:
Hi ProfessorJimJam,

I got your formula to work by adding ' to the sheet names and by adding ! to the first reference for Sheet2 as below

=INDEX('Sheet1'!C:C,MATCH(A2&B2,'Sheet2'!A:A&'Sheet2'!B:B,0))

Thanks for your help

swjtx99

Author

Commented:
Hi ProfessorJimJam,

Actually, this isn't working after all. Looks like it depends on the sort order to return the right value so it's not really "matching". Am I missing something?

To further explain. for the formula in Row 3, I get the value from sheet 1 row 3, not the row where the match was made.

Thanks,

swjtx99
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
check the attached file
Example.xlsx

Author

Commented:
Hi ProfessorJimJam,

This worked. I wasn't sure you'd look again at this since it was accepted so I submitted another question and got a different answer that also works so better to have 2 than none!

Thanks for the follow-up,

Regards,

swjtx99
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.