I have one master sheet where I have columns:

The requirement is that in sheet 2, I need all the names which has type S1. The output in sheet 2 should be:

Please suggest how to do it. I have tried vlookup and index match. it could not work out for similar name column search. 2. Need to know how to auto -populate the sheet without error based on any name added in master - sheet.

The things which I have tried are:

1. I moved the solumn Type to the left as column 1. then I used vlookup("S1",Array size,2,FALSE) It gave me the first name correct as it matched. but when I pull down the cell , the formulae applied with each array value shift and output was not correct. I attached $ as example: A3 -> A$3 to keep the table array same, but it gave me NA in some error when it did not match the S1 for the next row.

Thanks, sbx.

CodePudding user response：

I think the easiest solution is to use a if statement with arrays then filter the results. Similar to this equation.

=FILTER(IF($B$2:$B$6="S1",A2:A6,""),($B$2:$B$6="S1"))

The arrays are $B$2:$B$6 and A2:A6.

Thanks for the question

Update this is suggested by P.b and works very well.

=FILTER(A2:C6,B2:B6="S1")

CodePudding user response：

OP mentions on comment,

The FILTER() function worked and also auto-populate able to manage. Need to know how can we control the the number of required columns from FILTER() function, as we do in VLOOKUP(). we can get the data from the required column only. Please suggest. Example: I don't need the Type column again, as We already filtered based on Type columns so we know that this new sheet is for S1.

So here is one alternative for those who are using O365, presently on Insiders Beta Channel

=CHOOSECOLS(FILTER(A2:C6,B2:B6="S1"),1,3)

And if you are not in above Channel & using O365 as well, then, Double FILTER() Function

=FILTER(FILTER(A2:C6,B2:B6="S1"),{1,0,1})