Excel求助：B16下拉选A/B/C时，如何让D16对应返回0/1/2？

Hey there! Let's get this sorted out for you. The issue you're facing is super common, and there are a few solid ways to fix it depending on your Excel version and preference.

Method 1: Nested IF Functions (Works in All Excel Versions) #

If you want a straightforward formula without needing extra cells, a nested IF should do the trick. Chances are you might have missed wrapping text options in double quotes earlier—this is a common pitfall!

In cell D16, enter this formula:

=IF(B16="A", 0, IF(B16="B", 1, IF(B16="C", 2, "")))

• The final "" acts as a fallback if the cell is cleared or an unexpected value is selected. You can replace it with a message like "Invalid selection" if you prefer.

Method 2: VLOOKUP with a Lookup Table (Cleaner for Scaling) #

If you might add more options later, using a lookup table is way easier to maintain. Here's how:

1. Pick a blank section of your sheet (e.g., G1:H3) and set up your mapping:

   G1	H1
   A	0
   B	1
   C	2

2. In D16, use this VLOOKUP formula:

=VLOOKUP(B16, $G$1:$H$3, 2, FALSE)

• The $ signs lock the table range so it doesn't shift if you copy the formula to other cells.
• FALSE ensures it only matches exact values from your dropdown (no partial matches).

Method 3: SWITCH Function (Excel 2019/365 Only) #

If you're on a newer Excel version, SWITCH is the most readable option—it avoids messy nested IFs entirely:

=SWITCH(B16, "A", 0, "B", 1, "C", 2, "")

This reads like plain English: "If B16 is A, return 0; if B16 is B, return 1; etc."

Quick Troubleshooting for Your Previous Attempts #

If your old IF/LOOKUP formulas didn't work, check these common mistakes:

• Did you forget double quotes around "A", "B", "C"? Excel requires quotes for text values in formulas.
• For regular LOOKUP: Did you use an unsorted table? LOOKUP needs the lookup column to be sorted alphabetically—VLOOKUP with FALSE doesn't have this requirement, making it safer here.
• Typos or extra spaces: Ensure the text in your formula matches exactly what's in the dropdown (even a single extra space will break the match).

Give these a try, and let me know if you run into any hiccups!

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