Forum Discussion
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
appreciate your help! Thank you!
=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.
4 Replies
- 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))- thanks this worked!
=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.
- thanks, this worked as per requirement
