match() - lookup value has multiple match

[lezawang]

Hi
I want a user to enter city name is cell H1 then excel will return the row number using Match(). What to do in case we have the lookup value matching many in a column. Thank you.

Enter city name H1= Toronto

name	phone	address
mary1	62	toronto
john1	11	ottawa
alex1	33	montreal
adam1	66	toronto
linda1	1	ajax
danny1	51	toronto
liz1	79	ottawa

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[cunningAce]

Hi,

Would this help,

Book1
	A	B	C	D	E	F	G	H
1	name	phone	address					toronto
2	mary1	62	toronto					mary1
3	john1	11	ottawa					adam1
4	alex1	33	montreal					danny1
5	adam1	66	toronto
6	linda1	1	ajax
7	danny1	51	toronto
8	liz1	79	ottawa
Sheet1

Cell Formulas
Range	Formula
H2	{=IFERROR(INDEX($A$2:$A$8,SMALL(IF($C$2:$C$8=$H$1,ROW($C$2:$C$8)-ROW($C$2)+1),ROWS($H$1:H1))),"")}
H3	{=IFERROR(INDEX($A$2:$A$8,SMALL(IF($C$2:$C$8=$H$1,ROW($C$2:$C$8)-ROW($C$2)+1),ROWS($H$1:H2))),"")}
H4	{=IFERROR(INDEX($A$2:$A$8,SMALL(IF($C$2:$C$8=$H$1,ROW($C$2:$C$8)-ROW($C$2)+1),ROWS($H$1:H3))),"")}
H5	{=IFERROR(INDEX($A$2:$A$8,SMALL(IF($C$2:$C$8=$H$1,ROW($C$2:$C$8)-ROW($C$2)+1),ROWS($H$1:H4))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.