EXCEL for MAC v.16, Microsoft 365. How to populate cells in a Table based on the values entered into Entry cells using a reference table.

Use a lookup formula that always points to Table A, then copy it through Table B.

Assume:

• Table A is an Excel table named TableA.
• TableA has columns [1], [2], [3 or 4], [5], [6].
• In each row of Table B, the yellow “Entry” cell contains a value from 0–36.

1. Turn Table A into an Excel table

1. Select the range that contains Table A.
2. Press Ctrl+T (or use Home > Format as Table).
3. Confirm My table has headers.
4. In Table Design, rename it to TableA.

This enables structured references that stay valid when Table B is moved or copied.

2. Add the formulas in Table B

Suppose in Table B:

• The Entry cell in the first data row is E2.
• The four output cells in that row are C2, D2, F2, G2.

Use INDEX with MATCH to pull the 4 values from Table A based on the Entry value.

In C2 (first output cell):

=INDEX(TableA[1], MATCH($E2, TableA[3 or 4], 0))

In D2 (second output cell):

=INDEX(TableA[2], MATCH($E2, TableA[3 or 4], 0))

In F2 (third output cell):

=INDEX(TableA[5], MATCH($E2, TableA[3 or 4], 0))

In G2 (fourth output cell):

=INDEX(TableA[6], MATCH($E2, TableA[3 or 4], 0))

Explanation:

• MATCH($E2, TableA[3 or 4], 0) finds the row in Table A whose “3 or 4” value equals the Entry value.
• INDEX(TableA[1], …) (or [2], [5], [6]) returns the corresponding value from the desired column.
• The $ in $E2 fixes the column so the formulas can be filled sideways.

3. Fill down to create 10 rows

1. Select C2:G2.
2. Drag the fill handle down to row 11 (or double‑click it if the Entry column is filled). Each row of Table B now returns the 4‑cell set from Table A for its Entry value.

4. Copy Table B anywhere

Because the formulas use structured references to TableA, Table B can be:

• Copied to other locations on the same sheet.
• Copied to other sheets.

The formulas will still refer to TableA and will continue to populate the 4 cells based on the Entry value.

If Table B is also converted to an Excel table, the structured references will automatically adjust per row, and copying the entire table (including formulas) to other sheets will preserve the behavior.

References:

• Using structured references with Excel tables
• Use calculated columns in an Excel table
• Use Excel built-in functions to find data in a table or a range of cells