Excel formula in finding the data in the table (Thank you in advance!)

[SMIBCI]

Please help me in constructing the right formula in the file as attached? I've been searching for different excel options in the google but can't find one that matches my problem.

Thank you thank you for the help!

[JohnTopley]

Try

in N6

=MAX((OFFSET($A$3,MATCH($N$4,$A$4:$A$10,0),1,1,8)*(OFFSET($A$3,MATCH($N$4,$A$4:$A$10,0),1,1,8)<$N$5)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.


in N8

=INDEX($A$11:$I$11,,MATCH($N$6,INDEX($A$1:$I$11,MATCH($N$4,$A$1:$A$11,0),0),0))

[SMIBCI]

Thank you very much Sir JohnTopley. You are great and amazing!

[SMIBCI]

Thank you very much Sir JohnTopley. You are great and amazing!

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.