Google Sheet formula to check all rows and bring a third value-CodePudding

I'm trying to come up with a formula to look through two columns of data, which have most of the same cell values, just in different order, (Order Number and Order Number 2), to put the corresponding Event Value in the "Matched Event Value" cell.

So for instance, in row 1, N2 and O2 have the same value, "Order 1", so Q2 would populate with "123".

I've tried different versions of IF= and VLOOKUP=, but I haven't figured it out.

CodePudding user response：

a bit unclear on the scope\expected output but you may try this in cell Q1 cell:

={"Matched Event Value";INDEX(IF(LEN(N2:N) LEN(O2:O),IF(EQ(N2:N,O2:O),P2:P,0),))}

CodePudding user response：

In cell Q2 try adding this:

=IF(N2=O2,P2,0)

After that extend the formula to the lower cells.