I have set up a sample sheet at https://docs.google.com/spreadsheets/d/1i3ZCE_9IvVyhrb-uK-ofMT3f0H_En1SfLSjbC8oyZlk/edit?gid=1600420249#gid=1600420249.
If I obtain the MATCH in a column and then use the match value in an Index function, I get the right results. However, if I use the INDEX and MATCH together, the results are incorrect.
I am using the column names to refer to the cells. I am using the Tables feature that is introduced in Gsheets , similar to the feature in Excel. How to fix this?
If I use the cell reference, the index-match works fine as shown in the Expected column.
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().