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

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]name[/TD]
[TD="class: xl65, width: 64"]phone[/TD]
[TD="class: xl65, width: 64"]address[/TD]
[/TR]
[TR]
[TD="class: xl65"]mary1[/TD]
[TD="class: xl65"]62[/TD]
[TD="class: xl65"]toronto[/TD]
[/TR]
[TR]
[TD="class: xl65"]john1[/TD]
[TD="class: xl65"]11[/TD]
[TD="class: xl65"]ottawa[/TD]
[/TR]
[TR]
[TD="class: xl65"]alex1[/TD]
[TD="class: xl65"]33[/TD]
[TD="class: xl65"]montreal[/TD]
[/TR]
[TR]
[TD="class: xl65"]adam1[/TD]
[TD="class: xl65"]66[/TD]
[TD="class: xl65"]toronto[/TD]
[/TR]
[TR]
[TD="class: xl65"]linda1[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]ajax[/TD]
[/TR]
[TR]
[TD="class: xl65"]danny1[/TD]
[TD="class: xl65"]51[/TD]
[TD="class: xl65"]toronto[/TD]
[/TR]
[TR]
[TD="class: xl65"]liz1[/TD]
[TD="class: xl65"]79[/TD]
[TD="class: xl65"]ottawa[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.