double vlookup?

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
swjtx99Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ProfessorJimJamConnect With a Mentor Commented:
=index(sheet1!C:C,match(A1&b1,sheet2A:A&Sheet2!B:B,0))  press control shift enter
0
 
SimonCommented:
Use an intermediate formula (=A1& "_"&B1) on both sheets to concatenate columns A&B and then use that as the key for the vlookup
0
 
Wilder1626Commented:
Try this eexample below.

Just adjust based on your excel file.

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

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
swjtx99Author 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
0
 
swjtx99Author Commented:
Attached is an example sheet.Example.xlsx
0
 
swjtx99Author Commented:
Sorry, that was the wrong example sheet.
Example.xlsx
0
 
swjtx99Author 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
0
 
swjtx99Author 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
0
 
ProfessorJimJamCommented:
check the attached file
Example.xlsx
0
 
swjtx99Author 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.