Get same result without helper column (Array formula challenge for MrExcel Gurus)

[President]

please see table below , also file uploaded for easy reference in dropbox https://www.dropbox.com/s/gzftk0j51h083j0/Book1.xlsx?dl=0


the only issue, i have is, that how can i wrap this formula =IFERROR(INDEX($H$2:$H$16,SMALL(IF($A$2=$G$2:$G$16,ROW($G$2:$G$16)-MIN(ROW($G$2:$G$16))+1,""),ROW(1:1))),"")

inside =OFFSET(Sheet1!$A$6,0,0,MATCH("*",Sheet1!$A:$A,-1)-5,1) so that i only have one single formula in the sheet that i put in Data validation and it will give me the extract result

in simple words, i want to get rid of the cells from A5 downward.

is this even possible. i know it is not easy, but i have no doubt on your genius brains.

i would really appreciate your help.

Excel 2010
	A	B	C	D	E	F	G	H
1	Search states	DATA VALIDATION					State	Name
2	Maine	Edward	=OFFSET(Sheet1!$A$6,0,0,MATCH("*",Sheet1!$A:$A,-1)-5,1)				Kansas	Smith
3							Maine	Johnson
4							South Dakota	Williams
5	Search results						Montana	Jones
6	Johnson						Delaware	Brown
7	Taylor						Kentucky	Davis
8	Lopez						South Carolina	Miller
9	Edward						South Dakota	Wilson
10							Oregon	Moore
11							Maine	Taylor
12							Oregon	Anderson
13							Delaware	Thomas
14							Maine	Lopez
15							Kansas	Larsen
16							Maine	Edward
17
Sheet1

Cell Formulas
Range	Formula
A6	{=IFERROR(INDEX($H$2:$H$16,SMALL(IF($A$2=$G$2:$G$16,ROW($G$2:$G$16)-MIN(ROW($G$2:$G$16))+1,""),ROW(1:1))),"")}
A7	{=IFERROR(INDEX($H$2:$H$16,SMALL(IF($A$2=$G$2:$G$16,ROW($G$2:$G$16)-MIN(ROW($G$2:$G$16))+1,""),ROW(2:2))),"")}
A8	{=IFERROR(INDEX($H$2:$H$16,SMALL(IF($A$2=$G$2:$G$16,ROW($G$2:$G$16)-MIN(ROW($G$2:$G$16))+1,""),ROW(3:3))),"")}
A9	{=IFERROR(INDEX($H$2:$H$16,SMALL(IF($A$2=$G$2:$G$16,ROW($G$2:$G$16)-MIN(ROW($G$2:$G$16))+1,""),ROW(4:4))),"")}
A10	{=IFERROR(INDEX($H$2:$H$16,SMALL(IF($A$2=$G$2:$G$16,ROW($G$2:$G$16)-MIN(ROW($G$2:$G$16))+1,""),ROW(5:5))),"")}
A11	{=IFERROR(INDEX($H$2:$H$16,SMALL(IF($A$2=$G$2:$G$16,ROW($G$2:$G$16)-MIN(ROW($G$2:$G$16))+1,""),ROW(6:6))),"")}
A12	{=IFERROR(INDEX($H$2:$H$16,SMALL(IF($A$2=$G$2:$G$16,ROW($G$2:$G$16)-MIN(ROW($G$2:$G$16))+1,""),ROW(7:7))),"")}
A13	{=IFERROR(INDEX($H$2:$H$16,SMALL(IF($A$2=$G$2:$G$16,ROW($G$2:$G$16)-MIN(ROW($G$2:$G$16))+1,""),ROW(8:8))),"")}
A14	{=IFERROR(INDEX($H$2:$H$16,SMALL(IF($A$2=$G$2:$G$16,ROW($G$2:$G$16)-MIN(ROW($G$2:$G$16))+1,""),ROW(9:9))),"")}
A15	{=IFERROR(INDEX($H$2:$H$16,SMALL(IF($A$2=$G$2:$G$16,ROW($G$2:$G$16)-MIN(ROW($G$2:$G$16))+1,""),ROW(10:10))),"")}
A16	{=IFERROR(INDEX($H$2:$H$16,SMALL(IF($A$2=$G$2:$G$16,ROW($G$2:$G$16)-MIN(ROW($G$2:$G$16))+1,""),ROW(11:11))),"")}
A17	{=IFERROR(INDEX($H$2:$H$16,SMALL(IF($A$2=$G$2:$G$16,ROW($G$2:$G$16)-MIN(ROW($G$2:$G$16))+1,""),ROW(12:12))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

is this even possible.

Happy to be proved wrong but I don't believe so. Why not put the 'Search Results' in a column off to the right and hide the column?

 

[azumi]

Im confused here, what exactly your desired results from your table (State and Name)? And what condition, is that your data validation?

 

[President]

thank you Peter.

 

[President]

Im confused here, what exactly your desired results from your table (State and Name)? And what condition, is that your data validation?

the desired result is already there A2 and B2. question is how can you do the same thing without using the helper cells from cell A6 downward , i mean the cells that have these formulas in them =IFERROR(INDEX($H$2:$H$16,SMALL(IF($A$2=$G$2:$G$16,ROW($G$2:$G$16)-MIN(ROW($G$2:$G$16))+1,""),ROW(1:1))),"")

 

[President]

thank you Peter.

i come to the conclusion that it is not possible do get this done without the use of helper column.

you are proven right in this case