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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

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

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