I have four columns to compare against and a fifth one which contains the target value. I need to compare the four columns (2 each) and if they match then return the corresponding value in the fifth column. One of the columns requires comparing using the typical = sign while the other column requires checking if the other column contains part of the text. I tried using regexmatch
but I couldn't get the formula to return the values as I'd wanted.
Here is the link to the google sheets file: https://docs.google.com/spreadsheets/d/13RotkFBSOTylN6YOxPjzYDNWwYtCXo6Ewh6UtCi0i1E/edit?usp=sharing
try:
=ARRAYFORMULA(IFNA(VLOOKUP(IFNA(REGEXEXTRACT(A2:A15,
TEXTJOIN("|", 1, SORT(D2:D15, 1, 0))))&"♦"&B2:B15,
{D2:D15&"♦"&E2:E15, F2:F15}, 2, 0)))