SOLVED

# Need help building logic

Brass Contributor

# Need help building logic

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

4 Replies
best response confirmed by AKuma0411 (Brass Contributor)
Solution

# Re: Need help building logic

=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.

# Re: Need help building logic

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

# Re: Need help building logic

thanks, this worked as per requirement

# Re: Need help building logic

thanks this worked!
1 best response

Accepted Solutions
best response confirmed by AKuma0411 (Brass Contributor)
Solution

# Re: Need help building logic

=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.