Home > database >  How to do Vlookup using two search columns to compare with two result columns?
How to do Vlookup using two search columns to compare with two result columns?

Time:12-22

enter image description here

I have google sheets with tab #1 that have the columns listed in the picture. I have tab#2 with the same info in Columns "Origin" and "Destination" but in a different order. I am trying to have the "Contract Lane Key" column show up to the corresponding rows in tab#2. In other words, I am trying to write a Vlookup that will check the "Origin" and "Destination" listed in tab#2 with the "Origin" and "Destination" columns listed in tab#1, and once both are a match, have the "Contract Lane Key" column to show up in tab#2.

Is there a way to have two search columns and match them with two result columns, to then display the Key?

This is what my vlookup looks like in tab#2:

=vlookup(B2:C, 'tab#1'!B2:C, 1, False)

This is the order of columns in both tabs:

"Contract Lane Key" is Column A

"Origin" is Column B

"Destination is Column C

CodePudding user response:

Try this in 'tab#2'!A2:

=ArrayFormula(IF((B2:B="") (C2:C=""),,IFERROR(VLOOKUP(B2:B&C2:C,{'tab#1'!B2:B&'tab#1'!C2:C,'tab#1'!A2:A},2,FALSE),"no match")))

Keep in mind that I've written this site unseen regarding your actual sheets and data. If this doesn't work, consider sharing a link to the sheet itself.

CodePudding user response:

Use the & operator to concatenate two columns to create a new search column and a new results column.

=ARRAYFORMULA(VLOOKUP(B2:B&C2:C,{'tab#1'!B2:B&'tab#1'!C2:C,'tab#1'!A2:A},2,))
  • Related