基于多列查询的Excel公式需求：匹配性别年龄获取对应系数

Based on your scenario where Sheet1 holds a dataset with Gender, Age, and corresponding Coefficient, and you need to pull the matching Coefficient using inputs from Sheet4's D46 (Gender) and E46 (Age), here are two reliable formula solutions:

Option 1: INDEX + MATCH (Compatible with all Excel versions) #

This combo works across every Excel version, including older ones. Assuming your Sheet1 data is structured with:

• Gender in Column A
• Age in Column B
• Coefficient in Column C

Use this formula in the cell where you want the result (e.g., Sheet4!F46):

=INDEX(Sheet1!$C:$C, MATCH(1, (Sheet1!$A:$A=Sheet4!$D$46)*(Sheet1!$B:$B=Sheet4!$E$46), 0))

Note for pre-Excel 365 users: After typing the formula, press Ctrl+Shift+Enter instead of just Enter to confirm it as an array formula. Excel will add curly braces around it automatically (don't type them manually).

Option 2: XLOOKUP (For Excel 365/2021 or newer) #

If you're using a modern Excel version, XLOOKUP simplifies the multi-criteria match into a cleaner formula:

=XLOOKUP(1, (Sheet1!$A:$A=Sheet4!$D$46)*(Sheet1!$B:$B=Sheet4!$E$46), Sheet1!$C:$C)

This formula doesn't require array entry and handles the criteria natively.

Key Notes #

Data Structure Assumption: Ensure each (Gender, Age) pair in Sheet1 is unique. If there are duplicates, both formulas will return the first matching Coefficient.
Error Handling: To avoid #N/A errors when no match exists, wrap the formula in IFERROR:

=IFERROR(INDEX(Sheet1!$C:$C, MATCH(1, (Sheet1!$A:$A=Sheet4!$D$46)*(Sheet1!$B:$B=Sheet4!$E$46), 0)), "No matching coefficient found")

Or for XLOOKUP:

=IFERROR(XLOOKUP(1, (Sheet1!$A:$A=Sheet4!$D$46)*(Sheet1!$B:$B=Sheet4!$E$46), Sheet1!$C:$C), "No matching coefficient found")

内容的提问来源于stack exchange，提问作者user8107531