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, ...)
为什么vlookup显示的是公式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))

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。