[Bug] VLOOKUP formula search for matching items and sum calculation error, inconsistent with wps results

[zhonghaozhang]

在您提交此问题之前，您是否检查了以下内容？

• 这真的是个问题吗？
• 我已经在 Github Issues 中搜索过了，但没有找到类似的问题。

受影响的包和版本

0.5.3

复现链接

输入公式 =SUM(VLOOKUP(T(IF({1},G6:H6)),下拉菜单!$P:$R,3,0) * IF(COUNT(J10:K10)=2,K10-J10+1,"0"))

G6:H6 两个人名
下拉菜单!$P:$R 两列人名和费用数字

J10:K10 日期 比如2024-01-05 2024-01-01

计算结果只算出了一个人的合计
在wps中算出的是两个人的合计 或者说很多人的统计计算

预期行为

实际行为

运行环境

Chrome

系统信息

System:
OS: macOS 15.0.1
CPU: (10) arm64 Apple M1 Max
Memory: 1.12 GB / 64.00 GB
Shell: 5.9 - /bin/zsh
Binaries:
Node: 18.8.0 - ~/.nvm/versions/node/v18.8.0/bin/node
Yarn: 1.22.22 - ~/Library/pnpm/yarn
npm: 8.18.0 - ~/.nvm/versions/node/v18.8.0/bin/npm
pnpm: 8.10.0 - ~/Library/pnpm/pnpm
Browsers:
Chrome: 131.0.6778.265
Safari: 18.0.1

[univer-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

Origin Title: [Bug] VLOOKUP公式查找匹配项求和计算出错, 与wps结果不一致

Title: [Bug] VLOOKUP formula search for matching items and sum calculation error, inconsistent with wps results

---

Before you submitted this question, did you check the following?

• Is this really a problem?
• I've searched Github Issues but haven't found any similar issues.

Affected packages and versions

0.5.3

Recurrence link

Enter the formula =SUM(VLOOKUP(T(IF({1},G6:H6)), drop-down menu!$P:$R,3,0) * IF(COUNT(J10:K10)=2,K10-J10+ 1,"0"))

G6:H6 two names
Drop-down menu!$P:$R two columns of name and cost number

J10:K10 date such as 2024-01-05 2024-01-01

The calculation result only calculated the total for one person.
What is calculated in wps is the total of two people, or the statistical calculation of many people.

Expected behavior

Actual behavior

Running environment

Chrome

System information

System:
OS: macOS 15.0.1
CPU: (10) arm64 Apple M1 Max
Memory: 1.12 GB / 64.00 GB
Shell: 5.9 - /bin/zsh
Binaries:
Node: 18.8.0 - ~/.nvm/versions/node/v18.8.0/bin/node
Yarn: 1.22.22 - ~/Library/pnpm/yarn
npm: 8.18.0 - ~/.nvm/versions/node/v18.8.0/bin/npm
pnpm: 8.10.0 - ~/Library/pnpm/pnpm
Browsers:
Chrome: 131.0.6778.265
Safari: 18.0.1

[jikkai]

Please provide the minimal reproduction

[zhonghaozhang]

在您提交此问题之前，您是否检查了以下内容？

• 这真的是个问题吗？
• 我已经在 Github Issues 中搜索过了，但没有找到类似的问题。

受影响的包和版本

0.5.3

复现链接

https://stackblitz.com/edit/univer-sheets-vite-pxjpuvm4?file=index.html,src%2Fmain.js

输入公式 =SUM(VLOOKUP(T(IF({1},G6:H6)),下拉菜单!$P:$R,3,0) * IF(COUNT(J10:K10)=2,K10-J10+1,"0"))

G6:H6 两个人名 下拉菜单!$P:$R 两列人名和费用数字

J10:K10 日期 比如2024-01-05 2024-01-01

计算结果只算出了一个人的合计 在wps中算出的是两个人的合计 或者说很多人的统计计算

预期行为

### 实际行为 

运行环境

Chrome

系统信息

System: OS: macOS 15.0.1 CPU: (10) arm64 Apple M1 Max Memory: 1.12 GB / 64.00 GB Shell: 5.9 - /bin/zsh Binaries: Node: 18.8.0 - ~/.nvm/versions/node/v18.8.0/bin/node Yarn: 1.22.22 - ~/Library/pnpm/yarn npm: 8.18.0 - ~/.nvm/versions/node/v18.8.0/bin/npm pnpm: 8.10.0 - ~/Library/pnpm/pnpm Browsers: Chrome: 131.0.6778.265 Safari: 18.0.1

[univer-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

Before you submitted this question, did you check the following?

• Is this really a problem?
• I have searched Github Issues but found no similar issues.

Affected packages and versions

0.5.3

Recurrence link

https://stackblitz.com/edit/univer-sheets-vite-pxjpuvm4?file=index.html,src%2Fmain.js

Enter the formula =SUM(VLOOKUP(T(IF({1},G6:H6)), drop-down menu!$P:$R,3,0) * IF(COUNT(J10:K10)=2,K10-J10 +1,"0"))

G6:H6 Two names drop-down menu!$P:$R Two columns of names and cost figures

J10:K10 date such as 2024-01-05 2024-01-01

The calculation result only calculates the total of one person. The calculation in wps is the total of two people, or the statistical calculation of many people.

Expected behavior

### Actual behavior 

Operating environment

Chrome

System information

System: OS: macOS 15.0.1 CPU: (10) arm64 Apple M1 Max Memory: 1.12 GB / 64.00 GB Shell: 5.9 - /bin/zsh Binaries: Node: 18.8.0 - ~/.nvm/versions/node/ v18.8.0/bin/node Yarn: 1.22.22 - ~/Library/pnpm/yarn npm: 8.18.0 - ~/.nvm/versions/node/v18.8.0/bin/npm pnpm: 8.10.0 - ~/Library/pnpm/pnpm Browsers: Chrome: 131.0.6778.265 Safari : 18.0.1

[zhonghaozhang]

补充复现链接 https://stackblitz.com/edit/univer-sheets-vite-pxjpuvm4?file=index.html,src%2Fmain.js

合计列 A2 包含公式
现在结果: 500 只计算了第一个人
预期结果: 计算结果应为 1500 应该计算范围内的所有人 并不是一个人的 500

@jikkai

[univer-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

Supplementary reproduction link https://stackblitz.com/edit/univer-sheets-vite-pxjpuvm4?file=index.html,src%2Fmain.js

Total column A2 contains the formula
Now the result: 500 only the first person is counted
Expected results: The calculation result should be 1500. It should count everyone in the range, not 500 for one person.

@jikkai

[Dushusir]

Please help check the formula T @wpxp123456

Excel:

=T(IF({1},B2:C2)) gets 张先生 吴先生
=T(IF(TRUE,B2:C2)) gets 张先生

Univer:
=T(IF({1},B2:C2)) gets 张先生
=T(IF(TRUE,B2:C2)) gets 张先生

[univer-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

Please help check the formula T @wpxp123456

Excel:

=T(IF({1},B2:C2)) gets Mr. Zhang Mr. Wu
=T(IF(TRUE,B2:C2)) gets Mr. Zhang

Univer:
=T(IF({1},B2:C2)) gets Mr. Zhang
=T(IF(TRUE,B2:C2)) gets Mr. Zhang