Forum Discussion
Solved
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 inst...
=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.
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))
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!
