Google Sheets重复项匹配异常：VLOOKUP无法填充对应重复值如何解决？

The problem you're hitting is a classic quirk of VLOOKUP: it always returns the first matching value it finds in the lookup range. So when you have duplicate keys like B1 in your Sheet2, it keeps pulling the first 5 instead of the corresponding 6 from the second B1 entry in Sheet1.

Here's how to fix this by creating unique "composite keys" that pair each value with its occurrence count, ensuring you match the exact corresponding entry:

Step-by-Step Solution #

1. Core Idea #

We'll generate a unique identifier for each row by combining the original key (like B1) with a running count of how many times that key has appeared up to that row. For example:

• First B1 becomes B1_1
• Second B1 becomes B1_2
  This makes every entry unique, so our lookup can target the exact row we need.

2. Use INDEX + MATCH with Composite Keys #

Replace your existing VLOOKUP formula with this one in Sheet2 (adjust cell ranges if needed):

=INDEX(
  IMPORTRANGE("1XTOfpirZufwJKyiZuSN7QP-OKtgq8tREbe3nUJqhXwU","'1814'!C2:C11"),
  MATCH(
    B3 & COUNTIF($B$2:B3, B3),
    ARRAYFORMULA(
      IMPORTRANGE("1XTOfpirZufwJKyiZuSN7QP-OKtgq8tREbe3nUJqhXwU","'1814'!B2:B11") & 
      COUNTIFS(
        IMPORTRANGE("1XTOfpirZufwJKyiZuSN7QP-OKtgq8tREbe3nUJqhXwU","'1814'!B2:B11"),
        IMPORTRANGE("1XTOfpirZufwJKyiZuSN7QP-OKtgq8tREbe3nUJqhXwU","'1814'!B2:B11"),
        ROW(IMPORTRANGE("1XTOfpirZufwJKyiZuSN7QP-OKtgq8tREbe3nUJqhXwU","'1814'!B2:B11")),
        "<=" & ROW(IMPORTRANGE("1XTOfpirZufwJKyiZuSN7QP-OKtgq8tREbe3nUJqhXwU","'1814'!B2:B11"))
      )
    ),
    0
  )
)

3. What This Formula Does #

• COUNTIF($B$2:B3, B3): Counts how many times the current key (B3) has appeared from the start of Sheet2's B column up to the current row, giving us the occurrence number.
• The ARRAYFORMULA part generates identical composite keys for Sheet1's data, pairing each key with its own occurrence count.
• INDEX + MATCH then looks up the composite key from Sheet2 in Sheet1's composite keys, returning the exact corresponding value from column C.

4. Simplified Alternative (If You Can Edit Sheet1) #

If you have edit access to Sheet1, add a helper column (e.g., column D) with this formula to generate composite keys directly:

=B2 & "_" & COUNTIF($B$2:B2, B2)

Then in Sheet2, your lookup becomes much simpler:

=VLOOKUP(B3 & "_" & COUNTIF($B$2:B3, B3), IMPORTRANGE("1XTOfpirZufwJKyiZuSN7QP-OKtgq8tREbe3nUJqhXwU","'1814'!D2:C11"), 2, 0)

(Note: We swapped the column order in IMPORTRANGE to put the composite key first, which is required for VLOOKUP.)

Important Notes #

• Make sure you've authorized the IMPORTRANGE connection between the sheets first (you'll get a prompt the first time you use it).
• If your data ranges grow, adjust row numbers (like C2:C11) to cover all entries, or use open ranges like C2:C for dynamic expansion.

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