Return Nth Match Using Index Match

[gtbaseball7]

I'm sure this has been asked before and I tried searching for the answer in this forum and google...

i want to return the first, second, third... nth match using index match. i feel like this should be a lay up..

my formula currently looks something like this


=index("All Store Info", Small(index(match("Zip Code",Array of Zip Codes,0),0),nth),match("Store",Array of Stores,0))


I'm looking for all stores in a particular zip code


Thanks

[AlKey]

Since you need to return multiple values you would need something different an array formula

You need to make changes to this formula template and replace text with real ranges

=IFERROR(INDEX("All Store Info",SMALL(IF("Zip Codes"="Zip Code",ROW("Zip Codes")),ROWS(B$2:B2))),"")


***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

If still have trouble transposing this to real formula please upload workbook sample.

[gtbaseball7]

Thank you for your feedback. Here is the new (your) formula with ranges.

=INDEX('Open Stores'!$A$2:$AF$2248,SMALL(IF('Store Info by State'!$I7='Open Stores'!AD2,ROW('Open Stores'!$AD$2:$AD$2248)),ROW('Open Stores'!$AD$2:AD3)))

I'm getting an #NUM error

This is as an array formula too. {}

Thanks

[AlKey]

Here is an example

=IFERROR(INDEX($A:$A,SMALL(IF($C$2:$C$10=12532,ROW($C$2:$C$10)),ROWS(B$2:B2))),"")

Row\Col	A	B	C
1	Store	Stores within a Zip cdoe	Zip Codes
2	Store1	Store3	14532
3	Store2	Store6	12452
4	Store3	Store8	12532
5	Store4		14532
6	Store5		12452
7	Store6		12532
8	Store7		12452
9	Store8		12532
10	Store9		10390

[gtbaseball7]

=INDEX('Open Stores'!$B$2:$B$2248,SMALL(IF('Open Stores'!$AD$2:$AD$2248=AI$3,ROW('Open Stores'!$AD$2:$AD$2248)),ROWS(AI$6:AI6)))

Column B is the List of Stores. Column AD is the list of respective Zip Codes for those stores. AI3 is the Zip Code. AI6 is where I want the list of stores in that Zip Code to go.

I get a FALSE for the IF logical test; however, I know there are two stores in that Zip Code.

Thanks

[Tony Valko]

=INDEX('Open Stores'!$B$2:$B$2248,SMALL(IF('Open Stores'!$AD$2:$AD$2248=AI$3,ROW('Open Stores'!$AD$2:$AD$2248)),ROWS(AI$6:AI6)))

Reference the entire column B in the INDEX function:

=INDEX('Open Stores'!$B:$B,SMALL(IF('Open Stores'!$AD$2:$AD$2248=AI$3,ROW('Open Stores'!$AD$2:$AD$2248)),ROWS(AI$6:AI6)))

Still array entered.

[gtbaseball7]

It is not working... Please see attachment.

Thank you!

[azumi]

Please see this file hope this works

[gtbaseball7]

Azumi,

This worked perfectly! Thank you!

Thank you all for your help!