0

I have one master sheet where I have columns:

Name Type Role
Name1 S1 R1
Name2 S2 R2
Name3 S1 R3
Name4 S3 R2
Name5 S1 R5

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

Name Type Role
Name1 S1 R1
Name3 S1 R3
Name5 S1 R5

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.

SemiCode
  • 35
  • 1
  • 8
  • Please include your attempted formulae in the body of the question. Without it, the post is off-topic. Hint: `FILTER()`. – JvdV Apr 11 '22 at 17:10
  • even if() with and() could be made to work. – Solar Mike Apr 11 '22 at 17:12
  • 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 inVLOOKUP. we can get the data from the required column only. Please suggest. Example: I don;t need the Type colum again, as We already filtered based on Type columns so we know that this new sheet is for S1. Thanks – SemiCode Apr 11 '22 at 18:00

2 Answers2

3

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")
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Shane S
  • 1,747
  • 14
  • 31
  • 2
    `=FILTER(A2:C6,B2:B6="S1")` will spill all three columns filtered as the expected result in the question. – P.b Apr 11 '22 at 18:07
2

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})

FORMULA_SOLUTION

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32