Excel中如何用单元格引用替代VLOOKUP的常量列索引实现多列SUM？

Hey there! The problem with your original attempt (=SUM(VLOOKUP(A2,G22:J24,"{" & X2 & "}",FALSE))) is that you’re passing a text string ("{2,3}") instead of a genuine array. Excel can’t interpret that string as column indices for VLOOKUP. Here are two reliable solutions tailored to different Excel versions:

Solution 1: For Excel 365/2021 (Dynamic Array Support) #

Use TEXTSPLIT to convert the comma-separated text in cell X2 into a usable array:

=SUM(VLOOKUP(A2,G22:J24,TEXTSPLIT(X2,","),FALSE))

How it works: #

• TEXTSPLIT(X2,",") splits the text in X2 (e.g., "2,3") into a dynamic array {2,3}.
• VLOOKUP uses this array to fetch values from columns 2 and 3 of the matched row.
• SUM adds those two values together.

Bonus: Handle spaces in X2

If your X2 might have spaces (like "2, 3"), add TRIM to clean up the split values:

=SUM(VLOOKUP(A2,G22:J24,TEXTSPLIT(TRIM(X2),","),FALSE))

Solution 2: For Older Excel Versions (No Dynamic Arrays) #

Use SUMPRODUCT combined with text manipulation to convert X2’s content into an array:

=SUMPRODUCT(VLOOKUP(A2,G22:J24,--MID(SUBSTITUTE(X2,",",REPT(" ",LEN(X2))),ROW(INDIRECT("1:"&(LEN(X2)-LEN(SUBSTITUTE(X2,",",""))+1)))*LEN(X2)-LEN(X2)+1,LEN(X2)),FALSE))

How it works: #

• SUBSTITUTE(X2,",",REPT(" ",LEN(X2))) replaces commas with spaces to align each number.
• MID extracts each number from the aligned string, and -- converts the extracted text to numbers.
• ROW(INDIRECT(...)) creates a sequence of numbers to iterate over each value in X2.
• SUMPRODUCT handles the array operations without needing Ctrl+Shift+Enter.

Alternative: Use INDEX + MATCH (More Flexible) #

If you prefer avoiding VLOOKUP entirely, this formula works for both modern and older Excel versions (adjust array entry for old versions):

=SUM(INDEX(G22:J24,MATCH(A2,G22:G24,0),TEXTSPLIT(X2,",")))

For old Excel, replace TEXTSPLIT(X2,",") with the same text manipulation from Solution 2.

Key Notes: #

• Ensure the values in X2 are valid column indices for your lookup range (G22:J24 has 4 columns, so indices 1-4 are allowed).
• Test with your actual data to confirm the matched row returns the correct values.

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