Need help building logic

[AKuma0411]

Hi everyone,

I need help building logic in excel where in my wb, I have two sheets 

Sheet 2 has following data points: 

Here’s what I want:

I want to look at column M for all the instances >=1 and then pull set # from column A, It should only pull the instances for >=1 ignoring the zeros

I want this logic built in sheet 1. Attaching the wb for reference

appreciate your help! Thank you!

[#2 · Accepted solution]

@AKuma0411 

=INDEX(Sheet2!$A$2:$A$11,SMALL(IF(Sheet2!$M$2:$M$11>=1,ROW(Sheet2!$M$2:$M$11)-1),ROW(A1)))

This formula returns the expected results in my sheet. The formula has to be entered with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web. The formula is in sheet1 in cell B2 and filled down.

[#3]

I may be missing something, but your question lends itself to using modern excel array functions.

When you are dealing with two columns (or more) where you want to apply criteria to one column (the filter by column) and return the entries in another column (the return column), you can use the filter function:

=FILTER(A1:A25,M1:M25>=1)

or more legibly:

=LET(filterby_col,M1:M25,return_col,A1:A25,FILTER(return_col,filterby_col>=1))

[#4]

thanks, this worked as per requirement

[#5]

thanks this worked!