excel - Excel - Trying to compare two columns worth of data in separate sheets and if they match return a set of data from each sheet

Question

I have two sheets of data in the same workbook. If data from Sheet 1 column J, matches data from sheet 2 column F, then i want columns A-I from Sheet 1 + Column C from Sheet 2 added onto a third sheet

标签： excel

Answer 1

我有 Excel O365 的解决方案。设置A2为=FILTER(Sheet1!J:J, (Sheet1!J:J <> "") * NOT(ISNA(MATCH(Sheet1!J2:J, Sheet2!F:F)))

然后设置B2为=XLOOKUP(A2#,Sheet1!J:J,Sheet1!A:I)

并设置K2为XLOOKUP(A2#,Sheet2!F:F,Sheet2!C:C)