Google Sheets自动更新月份并拉取对应信用卡余额公式报错求助（HLOOKUP范围越界）

Hey Marcel, let's break down why your formula is throwing that "out of bounds range" error and get your automatic credit card balance pull working smoothly.

What's Wrong With Your Original Formula #

Let's look at your formula again:

=if(match($F$10,$B$16:$I$16,0),HLOOKUP(B2,$A$15:$J$26,17,FALSE),0)

You've got three key issues here:

1. Hardcoded row index is way too big: Your data range $A$15:$J$26 only has 12 total rows (26-15+1=12). Using 17 as the row index for HLOOKUP is way outside this range—this is the direct cause of your error.
2. HLOOKUP is using the wrong lookup value: HLOOKUP searches horizontally across the top row of your range. You're using B2 (a credit card name) as the lookup value, but you should be searching for the month $F$10 instead.
3. Unprotected MATCH in IF condition: If MATCH can't find $F$10 in your month headers, it returns a #N/A error instead of a boolean value. This breaks the IF statement entirely.

The Best Fix: INDEX + MATCH (More Reliable Than HLOOKUP) #

INDEX + MATCH is far more flexible and less error-prone for this kind of two-dimensional lookup. Here's the formula you should use:

=IFERROR(INDEX($B$16:$J$26, MATCH(B2, $A$16:$A$26, 0), MATCH($F$10, $B$15:$J$15, 0)), 0)

Let's break this down:

• INDEX($B$16:$J$26, [row], [column]): Pulls the value from your balance table at the specified row and column.
• MATCH(B2, $A$16:$A$26, 0): Finds the row number of the credit card name in B2 within your list of cards.
• MATCH($F$10, $B$15:$J$15, 0): Finds the column number of your current month (in $F$10) within the month headers.
• IFERROR(..., 0): Returns 0 if either match fails (e.g., a month isn't in the table or a card name is misspelled) instead of showing an error.

If You Prefer to Use HLOOKUP (Corrected Version) #

If you want to stick with HLOOKUP, here's the fixed formula:

=IF(ISNUMBER(MATCH($F$10, $B$16:$I$16, 0)), HLOOKUP($F$10, $B$15:$J$26, MATCH(B2, $A$15:$A$26, 0), FALSE), 0)

Key fixes here:

• Replaced the hardcoded 17 with MATCH(B2, $A$15:$A$26, 0) to dynamically get the correct row for your credit card.
• Changed the HLOOKUP lookup value from B2 to $F$10 (the month you're searching for).
• Wrapped the MATCH in ISNUMBER() to ensure the IF condition gets a valid boolean value (even if the month isn't found).

Quick Setup Check #

Double-check your table structure to make sure this works:

• $A$15: Header for credit card names (e.g., "Card Name")
• $B$15:$J$15: Month headers (e.g., "Jan", "Feb", ..., "Oct")
• $A$16:$A$26: List of your credit card names
• $B$16:$J$26: Corresponding monthly balances for each card

This should automatically pull the correct balance for the current month (from $F$10) and the selected credit card, no more out-of-bounds errors!

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