=IF(ISBLANK($G6),"", IF($L6="Length",(INDEX('PRODUCT SPEC'!I$3:I$253,MATCH($H6&$G6,'PRODUCT SPEC'!$A$3:$A$253&'PRODUCT SPEC'!$D$3:$D$253,0))), IF($L6="Cuff",(INDEX('PRODUCT SPEC'!K$3:K$253,MATCH($H6&$G6,'PRODUCT SPEC'!$A$3:$A$253&'PRODUCT SPEC'!$D$3:$D$253,0)))), IF($L6="Palm",(INDEX('PRODUCT SPEC'!M$3:M$253,MATCH($H6&$G6,'PRODUCT SPEC'!$A$3:$A$253&'PRODUCT SPEC'!$D$3:$D$253,0)))), IF($L6="Finger",(INDEX('PRODUCT SPEC'!O$3:O$253,MATCH($H6&$G6,'PRODUCT SPEC'!$A$3:$A$253&'PRODUCT SPEC'!$D$3:$D$253,0))))))
答案1
不喜欢在查找中连接值,但至少以下内容可以让您了解如何大幅减少长度:
=IF(ISBLANK($G6),"",INDEX('PRODUCT SPEC'!I$3:O$253,MATCH($H6&$G6,'PRODUCT SPEC'!$A$3:$A$253&'PRODUCT SPEC'!$D$3:$D$253,0),(MATCH($L6,{"Length","Cuff","Palm","Finger"},0)*2)-1))
答案2
对于如此长的公式,最好将其复制到文本编辑器并格式化,以便更好地跟踪发生的情况。
您的格式化公式:
=IF(
ISBLANK($G6)
,""
, IF(
$L6="Length"
,(
INDEX(
'PRODUCT SPEC'!I$3:I$253
,MATCH(
$H6&$G6
,'PRODUCT SPEC'!$A$3:$A$253&'PRODUCT SPEC'!$D$3:$D$253
,0
)
)
)
, IF(
$L6="Cuff"
,(
INDEX(
'PRODUCT SPEC'!K$3:K$253
,MATCH(
$H6&$G6
,'PRODUCT SPEC'!$A$3:$A$253&'PRODUCT SPEC'!$D$3:$D$253
,0
)
)
)
)
, IF(
$L6="Palm"
,(
INDEX(
'PRODUCT SPEC'!M$3:M$253
,MATCH(
$H6&$G6
,'PRODUCT SPEC'!$A$3:$A$253&'PRODUCT SPEC'!$D$3:$D$253
,0
)
)
)
)
, IF(
$L6="Finger"
,(
INDEX(
'PRODUCT SPEC'!O$3:O$253
,MATCH(
$H6&$G6
,'PRODUCT SPEC'!$A$3:$A$253&'PRODUCT SPEC'!$D$3:$D$253
,0
)
)
)
)
)
)
正如您所看到的,您的第二个IF
参数太多,可能是嵌套出了问题。我无法告诉您它应该是什么样子,因为我不知道背后的逻辑,但在格式化的版本上应该很容易修复