如何用Excel公式将子件对应父件零件号关联至BOM上传表

需求说明 #

• 读取Engineering Release工作表的大纲层级列，识别零件的父子关系
• 将父件的「Part No.」填入BOMUploadRelease工作表对应子件的「Parent」列
• 顶层父件（层级为1）、Engineering Release大纲列无内容时，「Parent」列留空
• 仅使用Excel公式，不使用VB

公式（适用于Excel 365/2021，在BOMUploadRelease的B2单元格输入后下拉） #

=LET(
    当前零件, A2,
    当前层级, XLOOKUP(当前零件, 'Engineering Release'!$B:$B, 'Engineering Release'!$A:$A, ""),
    父件层级, IF(当前层级="", "", 当前层级-1),
    IF(当前层级="" OR 当前层级=1, "", INDEX('Engineering Release'!$B:$B, MAX(IF(('Engineering Release'!$A:$A=父件层级)*('Engineering Release'!$B:$B<>"")*(ROW('Engineering Release'!$B:$B)<XLOOKUP(当前零件, 'Engineering Release'!$B:$B, ROW('Engineering Release'!$B:$B))), ROW('Engineering Release'!$B:$B), 0))))
)

旧版Excel兼容公式（无LET/XLOOKUP，输入后按Ctrl+Shift+Enter确认数组公式） #

=IFERROR(INDEX('Engineering Release'!$B:$B, MAX(IF(('Engineering Release'!$A:$A=IFERROR(VLOOKUP(A2,'Engineering Release'!$B:$A,2,FALSE)-1,""))*(ROW('Engineering Release'!$B:$B)<IFERROR(MATCH(A2,'Engineering Release'!$B:$B,0),0)), ROW('Engineering Release'!$B:$B), 0))),"")

说明：公式默认Engineering Release的大纲层级列为A列、零件号列为B列；BOMUploadRelease的子件零件号列为A列、Parent列为B列，可根据实际列位置调整引用

内容的提问来源于stack exchange，提问作者Joshua Johnson