INDEX MATCH to return a list based on a value

[chappers]

Ok, so ive two sheets. On sheet1 i want to enter a value which will then be queried in sheet2 and if matchning values are found then unique values be listed accordingly.

For example:

Sheet1 should look like this:

	A	B
1	Search	xyz
2
3	Unique values	bob
4		john

<tbody>
</tbody>

Sheet 2

	A	B
1	xyz.com	bob
2	abc.com	matt
3	def.com	bill
4	xyz.com	john
5	def.com	rick
6	abc.com	adam
7	xyz.com	john
8	xyz.com	john

<tbody>
</tbody>

Ive tried the following formula in sheet1 but it doesn't return a list of unique values:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #0057d6}</style>=INDEX(sheet2!B1:B6,MATCH("*"&B1&"*",sheet2!A1:A6,0))

 

[djuunk]

I'm hoping some more will pitch in ideas here, but I'll give you mine. Maybe my suggestion will work to your requirements or maybe it wont'.

Your sample sheets have me a little confused. Correct me if I'm wrong, but you are trying to search unique values that will be in column A to bring
information of that matching value from Column B correct?

It might seem a little too easy, but I would suggest a VLOOKUP formula. The sample Sheet 2 shows duplicate unique values with different matching values (xyz.com = bob or john, abc.com = matt or adam).
This sample in Sheet 2 is not unique as you described. If the list in Column A consists of truly unique values (xyz.com/hello135 or such), then a simple VLOOKUP formula for column index 2 will work.

Sheet 2 Cell B2 =VLOOKUP(sheet2CellA1, Sheet 1, 2, 0), double-click cell's bottom right corner to autofill formula down the entire list.
Lookup value = sheet2CellA1
Table array = Sheet 1, absolute reference ($A$1:$B$x)
Column index = 2 for second column of array
Range lookup = 0 for Exact Match, 1 for Approximate Match.

 

[AhoyNC]

This is an array formula that must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

Excel Workbook
	A	B
1	Search	xyz
2
3	Unique values	bob
4		john
5
Sheet 1

Excel Workbook
	A	B
1	xyz.com	bob
2	abc.com	matt
3	def.com	bill
4	xyz.com	john
5	def.com	rick
6	abc.com	adam
7	xyz.com	john
8	xyz.com	john
Sheet2

 

[chappers]

Unforunately this didnt work:

{=IFERROR(INDEX(Sheet2!$B$1:$B$8,SMALL(IF(FREQUENCY(IF(ISNUMBER(SEARCH($B$1,Sheet2!$A$1:$A$8)),MATCH(Sheet2!$B$1:$B$8,Sheet2!$B$1:$B$8,0)),ROW(Sheet2!$B$1:$B$8)-ROW(Sheet2!$B$1)+1),ROW(Sheet2!$B$1:$B$8)-ROW(Sheet2!$B$1)+1),ROWS($B$3:B3))),"")}

Any ideas?

 

[AhoyNC]

How did it not work? Did you get an error msg. / did it return the wrong answer???

 

[AhoyNC]

Remove the IFERROR part of the formula, so you can see if you are getting an error.
Make sure to enter the formula with CTRL-SHIFT-ENTER (command-return on MAC).

[COLOR=#000000][FONT=Arial]=INDEX(Sheet2!$B$1:$B$8,SMALL(IF(FREQUENCY(IF(ISNUMBER(SEARCH($B$1,Sheet2!$A$1:$A$8)),MATCH(Sheet2!$B$1:$B$8,Sh    eet2!$B$1:$B$8,0)),ROW(Sheet2!$B$1:$B$8)-ROW(Sheet2!$B$1)+1),ROW(Sheet2!$B$1:$B$8)-ROW(Sheet2!$B$1)+1),ROWS($B$3:B3)))[/FONT][/COLOR]

 

[chappers]

Sorry, got it working. There was a space in the formula so i just removed it:

{=IFERROR(INDEX(Sheet2!$B$1:$B$8,SMALL(IF(FREQUENCY(IF(ISNUMBER(SEARCH($B$1,Sheet2!$A$1:$A$8)),MATCH(Sheet2!$B$1:$B$8,Sh eet2!$B$1:$B$8,0)),ROW(Sheet2!$B$1:$B$8)-ROW(Sheet2!$B$1)+1),ROW(Sheet2!$B$1:$B$8)-ROW(Sheet2!$B$1)+1),ROWS($B$3:B3))),"")}