VLOOKUP Help

[riagil]

Dear excel master...

I have data like this :
-------------
Name | Agree
-------------
Apri | YES
Budi | YES
Candra | NO
Doni | YES
Edi | NO
-------------

I want to recap that data like this :
-------------
OK | NO
-------------
Apri | Candra
Budi | Edi
Doni |
-------------

I'm using

Please Login or Register  to view this content.

this the result :
-------------
OK | NO
-------------
Apri | Candra
Apri | Candra
Apri | Candra
-------------

Help me, and sorry for my bad english... Thank you for help..

[AlKey]

Try this array formula
Pull formula down and across

=IF(ROWS($A$1:A1)>COUNTIF($B$2:$B$6,D$1),"",INDEX($A:$A,SMALL(IF($B$2:$B$6=D$1,ROW($B$2:$B$6)),ROWS($A$1:A1))))

	A	B	C	D	E
1	Name	Agree		YES	NO
2	April	YES		April	Candra
3	Budi	YES		Budi	Edi
4	Candra	NO		Doni
5	Doni	YES
6	Edi	NO

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[jewelsharma]

If you are looking to sort names into YES/NO, VLOOKUP won't be very helpful.
(see attached example)Sort Names.xlsx
Notes:

• I've used Text-To-Columns to split the "Name" and "Agree"; and then
• use IF formula to do the sorting.

Hope this works for you.

[jewelsharma]

... or use the "Expert" Array Formula.

[riagil]

Thank you for reply Mr. jewelsharma

It works!!
You give me the wonderful answer , from newbie to expert, I prefer to use your second answer, on the first answer i still confuse using array.
Many thanks to you. And sorry for mt bad english...

[jewelsharma]

Happy to help mate! Trust you have a desired solution. Kindly mark the thread as SOLVED

Warm regards,
Jewel