Maybe I'm oversimplifying your issue, but from your explanation, I can infer that (Others1, Others2, Others3) = Global, if that is the case, then you can simply rename them before making the join, see this example:
library(dplyr)
library(stringr)
# Sample data on a copy/paste friendly format
Master_table <- data.frame(
stringsAsFactors = FALSE,
City = c("Area1","Area1","Area2",
"Area3","Others1","Others2","Others3"),
Dependency = c("Payment","Collection",
"Collection","Payment","Debit","e-Payment","DD"),
Product = c("PRD876","PRD543","PRD109",
"PRD598","PRD120","PRD501","PRD409"),
Type = c("D", "A", "C", "S", "A", "G", "X")
)
Dependency_table <- data.frame(
stringsAsFactors = FALSE,
City = c("Area1","Area1","Area2",
"Area3","Global","Global","Global"),
Dependency = c("Payment","Collection",
"Collection","Payment","Debit","e-Payment","DD"),
Start_Date = c("9/29/2020","9/29/2020",
"9/30/2020","10/1/2020","9/29/2020","9/30/2020",
"10/4/2020")
)
Master_table %>%
mutate(City = if_else(str_detect(City, "^Others"), "Global", City)) %>%
left_join(Dependency_table, by = c("City", "Dependency"))
#> City Dependency Product Type Start_Date
#> 1 Area1 Payment PRD876 D 9/29/2020
#> 2 Area1 Collection PRD543 A 9/29/2020
#> 3 Area2 Collection PRD109 C 9/30/2020
#> 4 Area3 Payment PRD598 S 10/1/2020
#> 5 Global Debit PRD120 A 9/29/2020
#> 6 Global e-Payment PRD501 G 9/30/2020
#> 7 Global DD PRD409 X 10/4/2020
Created on 2020-09-30 by the reprex package (v0.3.0)