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

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

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

Try this eexample below.

Just adjust based on your excel file.

Just adjust based on your excel file.

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

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

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

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

ASKER

Attached is an example sheet.Example.xlsx

ASKER

Sorry, that was the wrong example sheet.

Example.xlsx

Example.xlsx

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

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(

Thanks for your help

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

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

check the attached file

Example.xlsx

Example.xlsx

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

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