Forum Discussion
Update my formula to return "Yes" if a match is found, and if not, return the corresponding value
Hi, I'm using this formula =IFERROR(IF(MATCH(1, (Sheet1!A2=Sheet2!A:A) * (Sheet1!B2=Sheet2!B:B), 0), "Yes"), "No") to return "Yes" if a match is found, and if not, to return "No".
Now, I want to update it to return "Yes" if a match is found, and if not, it will return the corresponding value from column C of Sheet2.
Thanks for your formula. It's return me No like I used to do with my previous formula. But I would like to know the corresponding value in the column C for Sheet2.
I have two Sheets : Sheet 1 and Sheet 2. Both of them have data into columns A, B and C.
So, I am comparing Sheet1!A2, Sheet1!B2, Sheet1!C2 and Sheet2!A2, Sheet2!B2, Sheet2!C2
So if my formula return me No, I would like it to return me the corresponding value into column C from Sheet 2 as the value into columns A & B are the same (maybe A or B) in Sheet 1.
Perhaps this?
=XLOOKUP(Sheet1!A2, Sheet2!$A$2:$A$1000, Sheet2!$C$2:$C$1000, XLOOKUP(Sheet1!B2, Sheet2!$B$2:$B$1000, Sheet2!$C$2:$C$1000, "No"))
This will return the value from column C if column A or column B matches (or both).
I'm afraid I don't understand. If the values in columns A and B match, the formula that I posted will return the corresponding value from column V.
If there is no match, the formula returns "No". There is no corresponding value from column C since there is no match.
How can there be a corresponding value if there is no match?
Is it possible to update the formula to return a value if one of the cell value is matching?
=XLOOKUP(Sheet1!A2&B2, Sheet2!$A$2:$A$1000&Sheet2!$B$2:$B$1000, Sheet2!$C$2:$C$1000, "No")
(I wouldn't use entire columns for performance)
