Vlookup for Multiple Values

[karwanbk]

hello guys ,

i have problem with Vlookup
i have a sheet "board"
like this
B A
yes Jack
yes Ali
no Sara
yes Alex
no Martin
yes Tim

and in another sheet i have a list for only Yes
then i want list all the names with Yes in the "board" sheet
in next sheet want like

A
Jack
Ali
Alex
Tim

i used the VlookUp but it make repeat the names if i used like give me
A
Jack
Jack
... go on with jack -_-

and there is lot of rows in sheet "board"

thanks

 

[alocke]

Re: how to use Vlookup for Multiple Values

I'm not really sure what you are asking, but look at =INDEX() function as a potential solution...

How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP

 

[karwanbk]

Re: how to use Vlookup for Multiple Values

B A
yes Jack
yes Ali
no Sara
yes Alex
no Martin
yes Tim

that's columns in sheet "board"
i want list all names in column B that have Yes in Column A
and used INDEX() doesn't work

 

[karwanbk]

Re: how to use Vlookup for Multiple Values

any other suggestions ?

 

[Weazel]

Re: how to use Vlookup for Multiple Values

something like...


Unknown[TABLE="class: grid, width: 350"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]yes[/TD]
[TD]Jack[/TD]
[TD="align: right"][/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]yes[/TD]
[TD]Ali[/TD]
[TD="align: right"][/TD]
[TD]Ali[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]no[/TD]
[TD]Sara[/TD]
[TD="align: right"][/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]yes[/TD]
[TD]Alex[/TD]
[TD="align: right"][/TD]
[TD]Tim[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]no[/TD]
[TD]Martin[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]yes[/TD]
[TD]Tim[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

board​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IFERROR(INDEX(board!$B$2:$B$7,SMALL(IF(board!$A$2:$A$7="yes",ROW(board!$A$2:$A$7)-ROW(board!$A$2)+1),ROWS($D$2:D2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[karwanbk]

Re: how to use Vlookup for Multiple Values

working fine thanks

but not working like that
{=IFERROR(INDEX(board!$B$2:$B,SMALL(IF(board!$A$2:$A="yes",ROW(board!$A$2:$A)-ROW(board!$A$2)+1),ROWS($D$2:D2))),"")}

i made $B$2:$B, $A$2:$A, $A$2:$A
cause i don't know number of them