Excel跨工作表双列匹配取值求助：匹配ID1、ID2提取Value

Hey Stephanie, I totally get why standard VLOOKUP or basic INDEX/MATCH didn't work here—you're trying to match on two columns (ID1 and ID2) instead of just one, which requires a slight adjustment to those functions. Let's break down two reliable solutions that'll get your Tab2 Value column populated correctly:

Solution 1: INDEX + MATCH (Works for All Excel Versions) #

This method uses array logic to check both ID criteria at once. Here's the formula to enter in the first row of your new Value column in Tab2 (e.g., cell C2):

=INDEX(Tab1!$C:$C, MATCH(1, (Tab1!$A:$A=Tab2!$A2)*(Tab1!$B:$B=Tab2!$B2), 0))

• What each part does:
  • Tab1!$C:$C: The column in Tab1 where your target Value data lives (locked with $ so it doesn't shift when you drag the formula down).
  • (Tab1!$A:$A=Tab2!$A2)*(Tab1!$B:$B=Tab2!$B2): This creates an array of TRUE/FALSE values. The * acts like an "AND" operator—only rows where both ID1 and ID2 match will return 1 (TRUE*TRUE=1), others return 0.
  • MATCH(1, ..., 0): Finds the first row where the combined criteria equals 1 (exact match).

Note: If you're using an older Excel version (pre-365/2021), you'll need to press Ctrl+Shift+Enter to run this as an array formula instead of just Enter. Newer versions handle it automatically.

Solution 2: XLOOKUP (Simpler for Excel 365/2021+) #

If you have access to XLOOKUP (the newer, more flexible lookup function), you can combine your two IDs into a single "match key" for a cleaner formula:

=XLOOKUP(Tab2!$A2&Tab2!$B2, Tab1!$A:$A&Tab1!$B:$B, Tab1!$C:$C, "No Match")

• How it works:
  • Tab2!$A2&Tab2!$B2: Combines ID1 and ID2 from Tab2 into one string (e.g., "ID123ID456").
  • Tab1!$A:$A&Tab1!$B:$B: Does the same for Tab1's IDs, creating a list of combined keys to match against.
  • Tab1!$C:$C: The Value column to pull from when a match is found.
  • "No Match": Optional—replace this with whatever you want to show if no matching ID pair exists (leave it out to get #N/A instead).

Quick Tips to Avoid Mistakes #

• Always use absolute references (the $ signs) for your Tab1 ranges so they don't shift when you drag the formula down Tab2.
• If there are duplicate ID1+ID2 pairs in Tab1, both methods will return the Value from the first matching row. If you need to handle duplicates, filter or deduplicate Tab1 first.
• Test the formula on one row first before dragging it down—this helps catch typos or range errors early.

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