Need to find the largest value less than (not equal) to a lookup value using Index-match

Hi dear all

Need to find the largest value less than (not equal) to a lookup value from a table using Index-match (refer to attachment). As you see my formula in the cell H6 is


=INDEX(E5:F15,MATCH(H5,E5:E15,1),1)

and my Lookup value is the the value on cell H5 but when I select the match type value '1' it gaves me the value equal to my lookup value. is there any way to get only the smaller value than the lookup value?
any help is appreciated. tanx in advance

Hi, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

Having said that, try removing the ,1.....
=INDEX(E5:F15,MATCH(H5,E5:E15),1)

If it finds a match, it will return that match. Try this...
=INDEX(E5:E25,MATCH(H5,$E$5:$E$25)-IF(INDEX(E5:E25,MATCH(H5,$E$5:$E$25))=H5,1,0))

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)