How do I populate columns from Sheet(A) and Sheet(B) into Sheet(C) based on specific word/

Hello all,

My question is this: How do I populate columns from Sheet(A) and Sheet(B) into Sheet(C) based on specific word/s.

Attached is my example sheet with the sections laid out, just minus the formula I am asking you all for.

I want to populate SHEET(C) With Columns D (NAME), E (LAST 4), F (POSITION), G (TEAM).

- ONLY -

When the notes section has "NONPLAYABLE" in the value. AND/OR When Column "J" says "NO"

When the criteria is met above, it would copy the above columns, C, D, E, F, G, into SHEET 3 (Waivers) into their respective columns.

- Example -

SHEET 1
Steven 0024 C TEAM 1 NONPLAYABLE

*Auto Populate those columns*

WAIVER
Steven 0024 C TEAM 1 NONPLAYABLE

Maybe an array will do it for you like this:

{=IFERROR(INDEX('TEAM 1'!D3:D7,MATCH($C3,'TEAM 1'!$J$3:$J$7,0)),"")}

Put this in 'Waivers' D3 then press CTRL+SHIFT+ENTER to get the curly brackets. Drag across to L3 then drag down to L35.

Hope that helps,
Billy