The issue is caused by the evaluation of a range in an array enabled formula.

You’re getting the row index with this pattern:

match(Employees[Name], Employees[Name], 0)

When that is tested as a formula of its own, it gets the expected result. The first instance of Employees[Name] is evaluated to the current row in the table, and the formula gets just one result.

The index() function, however, array enables its parameters. In this context, the match() will go through all of the rows in the first parameter getting a vertical array of values, and match every one of those values to the column, which gives an array of results instead of a single result.

To make it work, move the match() out of the index(), so that it only returns one result, like this:

=let( 
  i, match(Employees[Name], Employees[Name], 0), 
  index(Employees[Name], i) 
)

Index-match is a legacy pattern. For most applications, it is easier to use functions like
query(),
filter(),
scan() or
chooserows().

Using Index and Match

Based on the current setup of your table, you are using an array as the search key. Adding [#This Row] to the formula will ensure it processes the values of the current row instead of the entire array.

You can try this formula:

=INDEX(Employees[Name], MATCH(Employees[[#This Row],[Name]],Employees[Name],0))

Sample Output:

Note: Simply drag the formula down.

Try this simple approach first;

Named ranges are properly defined:

Go to Data > Named ranges in Google Sheets and verify that your column names are correctly set up as named ranges.

Explicitly refer to the named ranges in both MATCH and INDEX:

Example;

” =INDEX(DataRange, MATCH(TargetValue, LookupRange, 0), ColumnIndex) “

Else Try this approach;

Double-check named ranges: Ensure that your named ranges correspond precisely to the intended columns (e.g., the ID column should only include IDs).

For MATCH, use single-column ranges like Name, not entire arrays.
Reconstruct the INDEX-MATCH Formula Using Named Ranges: Rewrite the formula explicitly to ensure compatibility. For example:

” =INDEX(Totals, MATCH(Name in current row, Name, 0)) “

Totals should be the named range corresponding to the column where you want to retrieve data.
Name should be a single-column range where MATCH looks for Name in current row.

If it still doesn’t match try this approach;

Temporarily replace the named ranges with direct cell references to ensure the logic is sound:

” =INDEX(A2:A10, MATCH(B2, B2:B10, 0)) ” #Check the named range definitions for potential discrepancies.

The condition in the FILTER function is always TRUE that’s why you get all the ROWS

ROWS(FILTER(Employees[Name],Employees[Name]=Employees[Name]))

I suggest you use

COUNTIF(Employees[Name], $B2)

in the first row and fill down the other cells bellow.