match() to return multiple results

[lezawang]

Hi I want to apply match() on a column but I want match() function to return not only the position of 1 item but the positions of multiple items since the occurrence is not only one. Is that possible.
For example in this example, want match() to return (1,2, and 3) for me if I tried to match test

=match("test",a1:a6,0)

test

test

hello

test

bye

whatever

<tbody>
</tbody>

 

[Scott Huish]

In that example, it should be 1, 2, and 4. Now you can create an array that contains those values along with FALSE for the non-matching values. But what are you trying to do with the returned result?

This for example, would return the last row with the matching value.

=MAX(IF(A1:A6="test",ROW(A1:A6)))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[sunny102]

Hi,

Apply the following array formula
=IFERROR(INDEX(ROW($A$2:$A$60), SMALL(IF($D$2=$A$2:$A$60, ROW($A$2:$A$60)-MIN(ROW($A$2:$A$60))+1, ""), ROW(A1))),"") press CTRL+SHIFT+ENTER

and drag the formula down

it assumes that your list is in column A (from A2 to A60 and matching value is in D2 in this case test should be in D2)

 

[lezawang]

Thanks for your reply. I have a table which has several columns. the first column has Name, second has phone number, third has Address, the last one has CityName. With Vlookup, I can use the Name as a lookup value to find phone number, address, city name.

I was thinking if I can reverse that by asking user to enter City Name and then excel will return all Names or phone numbers associated with that city.

I just tried your formula and pressed ctr+shift+enter but I got number "4" in the cell I put your formula.

Thanks once again.

In that example, it should be 1, 2, and 4. Now you can create an array that contains those values along with FALSE for the non-matching values. But what are you trying to do with the returned result?

This for example, would return the last row with the matching value.

=MAX(IF(A1:A6="test",ROW(A1:A6)))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[Aladin Akyurek]

If you want to know the native row numbers where "test" occurs...

IF(A1:A6="test",ROW(A1:A6))

If you want to know the positions within a range where "test" occurs...

=IF(A1:A6="test",ROW(A1:A6)-ROW(A1)+1)

Note that these formulas return an array of nil or more values...

 

[lezawang]

Sorry what I want to get these positions 1,2,4 saved somewhere. So I can go and use index() etc
Thanks

If you want to know the native row numbers where "test" occurs...

IF(A1:A6="test",ROW(A1:A6))

If you want to know the positions within a range where "test" occurs...

=IF(A1:A6="test",ROW(A1:A6)-ROW(A1)+1)

Note that these formulas return an array of nil or more values...

 

[Aladin Akyurek]

Sorry what I want to get these positions 1,2,4 saved somewhere. So I can go and use index() etc
Thanks

Since you want to save them, here is one way:

In C1 enter:

=COUNTIFS(A1:A6,"test")

In C3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($C$3:C3)>$C$1,"",SMALL(IF($A$1:$A$6="test",ROW($A$1:$A$6)-ROW($A$1)+1),ROWS($C$3:C3)))

Note. You can't 'save' such a result in a single cell; rather you can feed such a result to appropriate functions in a formula.