admin 管理员组

文章数量: 1086019


2024年3月21日发(作者：excel自动求和公式)

vlookup多条件比对公式

1. INDEX and MATCH Function:

Syntax:

=INDEX(return_range, MATCH(lookup_value1&lookup_value2,

lookup_range1&lookup_range2, 0))

Example:

Assume we have a dataset with three columns: Name, Age, and

Score. We want to look up the Score for a given Name and Age.

Using the INDEX and MATCH function, the formula would be:

=INDEX(Score, MATCH(Name1&Age1, Name&Age, 0))

2. SUMIFS Function:

The SUMIFS function is another approach to perform a

multiple criteria lookup in Excel. Although its primary purpose

is to add up values based on multiple conditions, it can also be

used to fetch a single value from a dataset.

Syntax:

=SUMIFS(sum_range, criteria_range1, criteria1,

criteria_range2, criteria2, ...)

Example:

Continuing with the previous example, assuming we have the

same dataset and want to fetch the Score for a given Name and

Age. The SUMIFS function can be used as follows:

=SUMIFS(Score, Name, Name1, Age, Age1)

3. Array Formulas:

Syntax:

{=INDEX(return_range, MATCH(lookup_value1&lookup_value2,

lookup_range1&lookup_range2, 0))}

Example:

To achieve a multiple criteria VLOOKUP using array formulas,

follow these steps:

Step 1: Select the range where you want to place the result.

Step 2: Enter the following formula using Ctrl+Shift+Enter:

=INDEX(Score, MATCH(Name1&Age1, Name&Age, 0))


本文标签： 公式 自动 求和 作者 条件

版权声明：本文标题：vlookup多条件比对公式 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.roclinux.cn/b/1711016571a584201.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。