CODEWITHSUNDEEP
google-sheets
Ted
Ted

Reputation: 77

Search two different columns and then copy value if match

I have two different sheets. Both of them have column A which is date.

Its sports related so sheet 1 has the team with stats and sheet 2 has the schedule.

Sheet1 includes the "team" and sheet2 includes "home" in column B and "visitor" in column C.

What i want to do is that if the dates match to look at columns "home" and "visitor" and if let's say "home"="team" then copy the "visitor" value.

So basically to find each teams opponent on each date.

Hope that's clear enough

Upvotes: 1

Views: 51

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A&C2:C, {Sheet2!A:A&Sheet2!D:D, Sheet2!C:C}, 2, 0)))

enter image description here

update:

=INDEX(IFNA(VLOOKUP(TRIM(A2:A&C2:C), 
 {TRIM(Sheet2!A:A&Sheet2!D:D), Sheet2!C:C; 
  TRIM(Sheet2!A:A&Sheet2!C:C), Sheet2!D:D}, 2, 0)))

enter image description here

Upvotes: 2

Related Questions