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,))