嵌套IF语句结合INDEX MATCH的Excel公式编写求助

Hey Aaron, no stress—let’s fix this formula for you right away!

Since you need to map AF2's three possible values to specific columns in the JIRA sheet, here are a couple of solid solutions, including the nested IF structure you asked for, plus a cleaner alternative if you're using a newer Excel version.

Nested IF Formula (Works in All Excel Versions) #

This follows the exact nested structure you wanted, checking each value in order:

=IF(AF2="Consultant", VLOOKUP(A2, JIRA!A:F, 6, FALSE),
 IF(AF2="Retailer", VLOOKUP(A2, JIRA!A:D, 4, FALSE),
 VLOOKUP(A2, JIRA!A:E, 5, FALSE)))

Quick Breakdown: #

• Replace A2 with the cell that contains the value you want to match against the JIRA sheet (I assumed it's column A—adjust if your match key is in a different column).
• The first IF checks if AF2 is "Consultant" and pulls from column F (6th column in the range JIRA!A:F).
• The second IF checks for "Retailer" and pulls from column D (4th column in JIRA!A:D).
• If neither matches, it defaults to "PC" and pulls from column E (5th column in JIRA!A:E).

Cleaner Alternative: SWITCH Function (Excel 365/2021+) #

If you have access to newer Excel features, SWITCH is way more readable than nested IFs:

=SWITCH(AF2,
 "Consultant", VLOOKUP(A2, JIRA!A:F, 6, FALSE),
 "Retailer", VLOOKUP(A2, JIRA!A:D, 4, FALSE),
 "PC", VLOOKUP(A2, JIRA!A:E, 5, FALSE))

This does the exact same thing but lays out each condition clearly without nested brackets.

Flexible Option: INDEX + MATCH + CHOOSE #

If you want to avoid hardcoding column numbers (in case your JIRA sheet columns move), this combo is great:

=INDEX(
 CHOOSE(MATCH(AF2, {"Consultant","Retailer","PC"}, 0), JIRA!F:F, JIRA!D:D, JIRA!E:E),
 MATCH(A2, JIRA!A:A, 0)
)

• MATCH finds which position your AF2 value is in the list (1=Consultant, 2=Retailer, 3=PC).
• CHOOSE picks the corresponding column from JIRA.
• INDEX + MATCH then pulls the value from the matching row in that column.

Just adjust A2 to your actual match key cell, and you’re good to go!

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