search

EPPlusSoftware / EPPlus

EPPlus-Excel spreadsheets for .NET
https://epplussoftware.com
Other
1.8k stars 273 forks source link

Excel formula involving multiple nested functions, an error occurs #1490

Closed SHAO-QII closed 2 months ago

SHAO-QII commented 4 months ago

EPPlus usage

Noncommercial use

Environment

windows

Epplus version

7.1.3

Spreadsheet application

excel

Description

When calculating with an Excel formula involving multiple nested functions, an error occurs. The error happens after successfully retrieving the value of E7 from a subset of data, but then fails when attempting to retrieve E6. Despite this, Excel executes the formula without indicating any errors

formula ： =IFERROR(LOOKUP(OFFSET(OFFSET(钢材库!$D$2:$D$102,MATCH(E6,钢材库!$M$2:$M$102,0)-1,0,COUNTIF(钢材库!$M$2:$M$102,E6),1),MATCH(E7,OFFSET(钢材库!$D$2:$D$102,MATCH(E6,钢材库!$M$2:$M$102,0)-1,0,COUNTIF(钢材库!$M$2:$M$102,E6),1))-1,0,1,1),OFFSET(钢材库!$D$2:$D$102,MATCH(E6,钢材库!$M$2:$M$102,0)-1,0,COUNTIF(钢材库!$M$2:$M$102,E6),1),OFFSET(钢材库!$E$2:$E$102,MATCH(E6,钢材库!$M$2:$M$102,0)-1,0,COUNTIF(钢材库!$M$2:$M$102,E6),1)),VLOOKUP(E6,钢材库!$C$2:$L$102,COLUMNS(钢材库!$C$2:$E$3),FALSE))

JanKallman commented 4 months ago

Can you attach the workbook that reproduces this issue.

SHAO-QII commented 4 months ago

ok！In the "阿姆斯图兹" worksheet, the strength values in row 13 need to be updated when the steel grade in row 6 is changed.

Test.zip

JanKallman commented 4 months ago

Works fine when I recalculates the workbook using EPPlus 7.2.

JanKallman commented 2 months ago

As I can not reproduce this, I will close this issue. If you still have problems with this, feel free to re-open this issue and add a fully reproducible sample.