MATCH Function returning same row number when searching for repeated text in the column.

Question

Level 1

12 points

MATCH Function returning same row number when searching for repeated text in the column.

Good Day, Team. Hope you're doing well.

I've been trying to use the MATCH function in numbers to search for a text in the column (that's repeated across multiple rows) and return the row number. However, it's giving the same row number all the time hence INDEX giving the same row many times in the result.

For Example, as posted below.

In Excel, there are functions like AGGREGATE and FILTER to use in this situation. However, I don't find these functions in Numbers.

Appreciate if you could help me shed some light as how to achieve the expected result in Numbers please.

Thanks Much in advance.

Regards

Posted on Jul 15, 2020 2:30 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,354 points

Jul 16, 2020 6:46 AM in response to BCM162

I'm not sure I understand your table and what formulas you used to get your results but you are correct that MATCH finds the first match in the specified range and stops there. Numbers does not have a FILTER function that provides all the matches in the specified range. With some cleverness you can get your results but it will not be a formula in a single cell that does it. There are multiple ways to do it, none of which are as clean as the FILTER function appears to be. Below is one method that is pretty straightforward.

In the example we are looking for all occurrences of the letter "T" in column C and want the results from column B.

Formula in D2 =IF(C2="T",ROW(),9999999)

Fill down from D2 to complete the column

Formula in cell E2 =IFERROR(INDEX(B,SMALL(D,ROW()−1)),"")

Fill down to complete the column

You can hide column D of Table 1 after everything is set up.

Reply

Answer 2

Yellowbox

Level 7

21,939 points

Jul 15, 2020 4:48 AM in response to BCM162

Hi BCM,

I am unsure of your aim.

What are you trying to look up?

What is the formula?

Please post a screen shot after selecting the cell that contains the formula. Click again to bring up the Formula editor so that we can see the formula.

More information will lead to a solution!

Regards,

Ian.

Reply