Formula to find the next match on the sheet (equal to some columns, within a range for other columns, and not equal to another column)
I am currently using index-match. I am open to other options, as this seems to not work. Column A is the rank - used for filtering and ranking data from the raw input, Column B is the index number of the row of data.
Current formulas:
=INDEX(B3:$B$5001,MATCH(1,(E2=E3:$E$5001)*(G2=G3:$G$5001),0))
This returns the first index number that matches two criteria.
=INDEX(B3:$B$5001,MATCH(1,(E2=E3:$E$5001)*(G2=G3:$G$5001),0)*MATCH(F2,M3:$M$5001,-1))
This was supposed to return the first index number that matches two criteria and is greater than a third cell of data (Column L is the minimum of the range and M is the maximum of the range). The intent was to have one match filter for the min and the other to filter for the max. But the result here is not as expected. It is returning the second result that should match the criteria.
=INDEX(B3:$B$5001,MATCH(1,(E2=E3:$E$5001)*(G2=G3:$G$5001),0)*MATCH(F2,M3:$M$5001,-1)*MATCH(TRUE,INDEX(D2:D3000<>D2,0),0))
That should return an index that matches two criteria, is greater than another, and is not equal to another. What this returns is completely incorrect.
Help!
Posted Dec 12, 2023, 5:46 PM UTC · locked
u/bagehis - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.