0

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.

Improve this question
3
  • I do think that this question is greatly related to your previous question. The provided answers to your previous post were working on the current question. stackoverflow.com/questions/79268321/… Commented Dec 13, 2024 at 6:17
  • Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. Commented Dec 13, 2024 at 7:10
  • 1
    Based on your comments in both the questions, It seems like you are specifically after a feature in TABLES(Employees[@Name] where @Name uses the Name value of the current row) that exists in Excel. I dont know if this very specific thing exists in Sheets or not but it could help to share screenshots(or screen recording gif) showcasing how this Employees[@Name] feature works in Excel so that people can start working in that exact angle bcoz it looks like you are not looking for an alternative per se., but for that feature to work/exist in Sheets. Commented Dec 13, 2024 at 9:10

4 Answers 4

Reset to default
2

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().

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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:

Output

Output 2

Note: Simply drag the formula down.

Improve this answer

1 Comment

I have added a column based on your suggestion. The This row automatically converts to absolute reference like $B2, $B3 etc. That is what I would like to avoid and instead use Column name. Tables and columns is a new feature of Google sheets and that is what I would like to use.
0

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.

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

In Excel, I can use ROWS(FILTER(Employees[Name],Employees[Name]=Employees[@Name])) . The @Name uses the Name value of the current row. How to get that effect in Google sheets using the new Tables feature which lets you use column names instead of absolute cell references?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.