VLOOKUP cell reference not returning expected values

[okcsteve]

When using a cell reference for the lookup value in the vlookup formula, using FALSE for the Range_lookup produces an expected #N/A when the lookup value is not an exact match to any of the records in the database. When there is not an exact match I would like to use the next lower number, and the definition indicates that using TRUE for the Range_lookup should work. However, it does not. How can I get it to work when the lookup value is not an exact match?

See sample file attached.

[okcsteve]

I will post a small sample workbook in just one minute.

[Jonmo1]

The Data in the lookup range must be sorted ASCENDING by the leftmost column in order for the TRUE to work.

[okcsteve]

I wish I could reorder it in ascending order but the nature of this does not allow for that. Is there a way to look for the next closest lower value within a specified range without having to resort the records?

[Jonmo1]

Not with vlookup, no.

It would help if you put your formula in the document somewhere and indicate what the expected result is.

[okcsteve]

Trying but I am discovering that this is easier said than done. Will post it as soon as I can work it out. Thanks.

[okcsteve]

The Data in the lookup range must be sorted ASCENDING by the leftmost column in order for the TRUE to work.

I need to find a way to execute vlookup (or an equivalent thereof) on an unmatched value without having to reorder my array every time. I tried using the =MATCH() function as the value argument in the =VLOOKUP () formula but could not get that to work out. Is there a way to use a different function that would find the greatest value in an array that is less than or equal to a specified value without having to reorder my array? See Cell J8 in the attached file. I need to get a value (in Column H) in the unordered array (F18:H146) corresponding to a value (0.81% in Cell H8) that does not have an exact match.

[Kringeling]

So what are you trying to get in the cell J8?

If I use the Vlookup(H8;F20:H146;3;TRUE) i get the value 0,263158

But i'm guessing this isn't the value you're after...

[okcsteve]

So what are you trying to get in the cell J8?

If I use the Vlookup(H8;F20:H146;3;TRUE) i get the value 0,263158

But i'm guessing this isn't the value you're after...

The value I am looking for is 3.01%, which is found by using =VLOOKUP(H8,F20:H146,3,TRUE) but the array must be ordered in ascending order. I am trying to find a solution to get the same value without having to order the array.

[Jonmo1]

Try this array formula entered with CTRL + SHIFT + ENTER
=INDEX($H$20:$H$146,MATCH(MAX(IF($F$20:$F$146<=H8,$F$20:$F$146)),$F$20:$F$146,0))

[okcsteve]

Try this array formula entered with CTRL + SHIFT + ENTER
=INDEX($H$20:$H$146,MATCH(MAX(IF($F$20:$F$146<=H8,$F$20:$F$146)),$F$20:$F$146,0))

JonMo1 - your formula nails it. I don't yet understand why this was the way to do it so I will need to study it. Thx so much!