excel xlookup search for a value in sheet 1, column B, in a 2nd workbook in columns D to K, and if found return the value from column M of the 2nd workbook. My values from Column B of sheet 1 could be in any of D:K and possibly in multiple, but they will all tie to the same value in M.
=XLOOKUP(1, (Workbook2!$D:$D=B2) + (Workbook2!$E:$E=B2) + (Workbook2!$F:$F=B2) + (Workbook2!$G:$G=B2) + (Workbook2!$H:$H=B2) + (Workbook2!$I:$I=B2) + (Workbook2!$J:$J=B2) + (Workbook2!$K:$K=B2), Workbook2!$M:$M, "Not Found", 0)
Do you have a question about that formula or did you just want to share a solution for a problem you faced?
Replicated your situation and the formula came out like below in my Excel. And it produces results in different situations. Wether these are correct or not I can't judge. If you need help, please upload examples of Workbook1 and Workbook2.
=XLOOKUP(1, ([Workbook2.xlsx]Sheet1!$D:$D=B2) + ([Workbook2.xlsx]Sheet1!$E:$E=B2) + ([Workbook2.xlsx]Sheet1!$F:$F=B2) + ([Workbook2.xlsx]Sheet1!$G:$G=B2) + ([Workbook2.xlsx]Sheet1!$H:$H=B2) + ([Workbook2.xlsx]Sheet1!$I:$I=B2) + ([Workbook2.xlsx]Sheet1!$J:$J=B2) + ([Workbook2.xlsx]Sheet1!$K:$K=B2), [Workbook2.xlsx]Sheet1!$M:$M, "Not Found", 0)