Excel多值Lookup：基于ID关联Table-1与Table-2实现名称填充

Got it, let's work through this problem together. You need to populate the name columns in Table2 with matching names from Table1 based on the ID_To_Compare value, keeping the order of names as they appear in Table1. Here's how to do it, depending on your Excel version:

First, Let's Confirm Our Table Structures #

Just to make sure we're aligned, here's the source and target data we're working with:

Table1 (Source Data) #

Table2 (Target Table) #

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

If you're using a modern Excel version with dynamic arrays, this is the simplest approach. The FILTER function will automatically spill all matching names into adjacent columns without extra work.

In the first cell of your name columns (e.g., cell B2 where A2 is ID_To_Compare=1), enter this formula:

=FILTER(Table1[NAMES], Table1[ID]=A2)

Hit Enter, and Excel will populate name1, name2, and name3 with all matching names from Table1 in order. If you add more names to Table1 later, this will update automatically.

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

For older Excel versions that don't support dynamic arrays, we'll use a combination of INDEX, SMALL, and IF to pull each matching name individually. These are array formulas, so you'll need to press Ctrl+Shift+Enter instead of just Enter after typing each one.

For name1 (first matching name) #

In cell B2:

=INDEX(Table1[NAMES], SMALL(IF(Table1[ID]=$A2, ROW(Table1[ID])-MIN(ROW(Table1[ID]))+1), 1))

For name2 (second matching name) #

In cell C2, just change the final 1 to 2:

=INDEX(Table1[NAMES], SMALL(IF(Table1[ID]=$A2, ROW(Table1[ID])-MIN(ROW(Table1[ID]))+1), 2))

For name3 (third matching name) #

In cell D2, change the final number to 3. To avoid getting #NUM! when there are fewer than 3 matches, wrap it in IFERROR:

=IFERROR(INDEX(Table1[NAMES], SMALL(IF(Table1[ID]=$A2, ROW(Table1[ID])-MIN(ROW(Table1[ID]))+1), 3)), "")

Once you have these formulas set up for the first row of Table2, drag them down to apply to all ID_To_Compare values.

Quick Notes #

• Format your data as official Excel Tables (use Ctrl+T) so references update automatically if you add more rows to Table1.
• The names will appear in Table2 in the same order they show up in Table1, which matches your desired outcome.

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