How to identify a row number considering category my values falls into

Question

Hello.&nbsp;I have a value of X (let's say it's 1000).I want to identify a row that X falls into between the two values of columns A and B, for exampleROW&nbsp; &nbsp;Column A&nbsp; &nbsp; &nbsp; &nbsp;Column B1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Min length&nbsp; &nbsp; &nbsp; Max length2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 800&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9003&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 901&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10004&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1100&nbsp;How can I do this? I have a lot of data such as this. My example is just a very simplified version of it.

oliverscheurich · Answer

TheNotSoGoodExcelGuy&nbsp;=MATCH(1,(D2&gt;=$A$2:$A$4)*(D2&lt;=$B$2:$B$4),0)+1You can try this formula if you want to return the row number. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.

Forum Discussion

How to identify a row number considering category my values falls into

1 Reply