Formula to match a value and return multiple results?

[haste]

Hi there,

I'm looking for a formula to match a value within an array and return the results found.

I know how to you use vlookup/match/index formula's, but I can only return the first match found. I would like to return the first match in cell A1 and the second match inside A2, third inside A3, etc.

So when the first match is found and placed inside A1 then the formula inside A2 should ignore the first match that is already found and show the next match value.

I would like to get this done with a formula and not with the autofilter option inside Excel.

I created an example: http://www.haste.nl/formula-filter.zip

Thanks for helping out!

 

[Marcelo Branco]

Hi and welcome,

Try this Array-formula in B4

=IF(COUNTIF($E$2:$E$17,$B$2)>=ROWS($B$4:B4),INDEX($D$2:$D$17,SMALL(IF($E$2:$E$17=$B$2,ROW($E$2:$E$17)-ROW($E$2)+1),ROWS($B$4:B4))),"")

confirmed with Ctrl+Shift+Enter

copy down

M.

 

[haste]

Hi Marcelo,

This is the formula I was looking for. Your quick reply is really amazing!

Thanks a lot for your help!

Regards
Haste

 

[T. Valko]

Hi there,

I'm looking for a formula to match a value within an array and return the results found.

I know how to you use vlookup/match/index formula's, but I can only return the first match found. I would like to return the first match in cell A1 and the second match inside A2, third inside A3, etc.

So when the first match is found and placed inside A1 then the formula inside A2 should ignore the first match that is already found and show the next match value.

I would like to get this done with a formula and not with the autofilter option inside Excel.

I created an example: http://www.haste.nl/formula-filter.zip

Thanks for helping out!

See this...

Lookup when there are multiple instances of the lookup value

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

 

[Marcelo Branco]

Hi Marcelo,

This is the formula I was looking for. Your quick reply is really amazing!

Thanks a lot for your help!

Regards
Haste

You are welcome and tks for the feedback

M

 

[haste]

See this...

Lookup when there are multiple instances of the lookup value

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

Thanks T. Valko

I've searched for something like this, but it's hard to find when English is not your main language. So I was only running into the standard formulas.

Great that this forum gives so much support for Excel!

 

[T. Valko]

Thanks T. Valko

I've searched for something like this, but it's hard to find when English is not your main language. So I was only running into the standard formulas.

Great that this forum gives so much support for Excel!

You're welcome. Thanks for the feedback!

 

[haste]

Curious if you can help me out with my next question.

What if I also want to perform a check on the result and make sure that the result returned (ID) is unique?

Let's say I'm looking for ID's that match status A and it returns:
1000
1001
1003
1000
1004

Now it's showing ID 1000 twice, but I only want it once. Is this possible?

 

[Marcelo Branco]

Maybe this Array-formula in B4 (Excel 2007 or higher)

=IFERROR(INDEX($D$2:$D$17,SMALL(IF($E$2:$E$17=$B$2,IF(COUNTIF(OFFSET($D$2,0,0,ROW($D$2:$D$17)-ROW($D$2)+1,1),$D$2:$D$17)=1,ROW($E$2:$E$17)-ROW($E$2)+1)),ROWS($B$4:B4))),"")

Ctrl+Shift+Enter

copy down

M.

 

[T. Valko]

Curious if you can help me out with my next question.

What if I also want to perform a check on the result and make sure that the result returned (ID) is unique?

Let's say I'm looking for ID's that match status A and it returns:
1000
1001
1003
1000
1004

Now it's showing ID 1000 twice, but I only want it once. Is this possible?

Try this...

Book1
	A	B	C	D	E
1	Status	ID	_	Status	ID
2	A	1001	_	A	1001
3	A	1005	_	_	1005
4	A	1005	_	_	_
5	A	1005	_	_	_
6	B	1000	_	_	_
7	B	1002	_	_	_
8	B	1003	_	_	_
9	B	1004	_	_	_
10	C	1002	_	_	_
11	C	1002	_	_	_
12	C	1005	_	_	_
13	D	1002	_	_	_
14	D	1005	_	_	_
15	D	1005	_	_	_
Sheet1

Enter this formula in E2:

=IFERROR(INDEX(B2:B15,MATCH("A",A2:A15,0)),"")

Enter this array formula** in E3:

=IFERROR(INDEX(B$2:B$15,MATCH(1,(A$2:A$15="A")*(COUNTIF(E$2:E2,B$2:B$15)=0),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.