r/excel•
bagehis

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)

unsolved

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

13
AutoModeratorMOD•

u/bagehis - Your post was submitted successfully.

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.

1
Clippy_Office_AsstMOD•

This post has been removed due to Rule 2 - Poor Post Body.

Please post with a proper description in the body of your post.

The body of your post should be a detailed description of your problem. Providing samples of your data is always a good idea as well.

Putting your whole question in the title, and then saying the title says it all is not a sufficient post.

Links to your file, screenshots and/or video of the problem should be done to help illustrate your question. Those things should not be your question.

Here's a long example and a short example of good posts.

Rules are enforced to promote high quality posts for the community and to ensure questions can be easily navigated and referenced for future use. See the Posting Guidelines for more details, and tips on how to make great posts.

1
excelevatorMOD2654•

Without the title, the post makes little sense as the key point of your issue is not explained.

As per our guidelines the post must stand alone from the title.

1