Avatar of swjtx99
swjtx99
 asked on

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
Microsoft Excel

Avatar of undefined
Last Comment
swjtx99

8/22/2022 - Mon
Simon

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

Try this eexample below.

Just adjust based on your excel file.

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

Open in new window

ASKER CERTIFIED SOLUTION
Professor J

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
swjtx99

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
swjtx99

ASKER
Attached is an example sheet.Example.xlsx
swjtx99

ASKER
Sorry, that was the wrong example sheet.
Example.xlsx
swjtx99

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
swjtx99

ASKER
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 J

check the attached file
Example.xlsx
swjtx99

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck