Place values into desired results column based using Status heading in column E

Looking for formula to place values into N2:N42 using Sales City & YR & Name (column E) and Status Heading in column F as the basis for placing values. In column F (Status), we have two values, "Nope" and "Yep". We will use Yep (column F) as our main focus to place the values in column N2:N42.

In our sample file, Yep is in F10:F10, values in G10:M13 will be considered and placed in N2:N42 using Sales City & YR & Name (column E) to match each value in G10:M13 for placement in N2:N42.

Example 1: 1999 - 2806 - Ben, Box G. - Cat Duck is in G12. We will then check Sales City & YR & Name (column E) to find where 1999 - 2806 - Ben, Box G. - Cat Duck is located. It is in E7 and the formula will place G12 value 1999 - 2806 - Ben, Box G. - Cat Duck in N7.

Example 2: 1999 - 2806 - Kevin, Spice A. - Cat Duck is in G13. We will then check Sales City & YR & Name (column E) to find where 1999 - 2806 - Kevin, Spice A. - Cat Duck is located. It is in E9 and the formula will place G13 value 1999

Thanks.

can you explain a bit further , I dont quite understand - you have a red fill for the "YEP", but nothing in M10 - M13
ROW 10- 13

Taking the first example
M3
1999 - 5535 - Ben, Box G. - 4Kor
Why is that a required output ? in M3

etaf: 1999 - 5535 - Ben, Box G. - 4Kor s a required output in M3 because we have to trace 1999 - 5535 - Ben, Box G. - 4Kor to where it occur in column E using Sales ID (column A), Sales City (column B), Sales YR (Column C), Last name and first (column D). For 1999 - 5535 - Ben, Box G. - 4Kor, 1999 is in C2, 5535 is in A2, Ben, Box G. in D2 and 4kor in column B. Column E is the concatenated and that is where you want to look to match each "Yep" values for our desired output.

can you explain a bit further , I don't quite understand - you have a red fill for the "YEP", but nothing in M10 - M13. Used the red fill to highlight the main requirement driver for the formula setup. For this example, Sales ID 4651 from A10:A13 has "Yep" and others Nope. This means values in G10:M13 will be placed in column N2:N42. For this sample, G10:M11 has no values, thus, the formula will not pickup anything here. If G10:M11 were to have values, the formula will be setup to place them in their respective row in N2:N42. For this sample, we only have values for "Yep" in G12:M12 and G13:I13.

ROW 10- 13

See attached sample file. Thanks.

sorry, still not sure i follow, hopefully someone else will be able to answer

Please try at M2
=IF(SUMPRODUCT(COUNTIFS(OFFSET($F$2:$F$42,,COLUMN(F$1:L$1)-COLUMN(F1)),D2,$E$2:$E$42,"Yep")),D2,"")

For 50,000 row, better use helper column

N2 =F2&G2&H2&I2&J2&K2&L2

O2 =IF(COUNTIFS($E$2:$E$42,"Yep",$N$2:$N$42,"*"&D2&"*"),D2,"")


FYI. No need Ctrl+Shift+Enter doesn't mean it's not Array formula.
Just because of some function eg; Sumproduct, Lookup, Index, frequency can handle array calculation without the need of Ctrl+Shift+Enter.