Thread: Using lookup function that locates the nearest lowest AND greatest value?

Hello,

I'm trying to solve this bit of function problem.

I'm trying to use vlookup function in table 2 referencing table 1 using the approximate parameter. However, an approximate vlookup value is by definition the closest *least* value. Is there a trigger of some sort to locate the closest *greatest* value as well?

For instance, if the lookup_value in table 2 is 2.450 but the referenced table 1 only has 2.445 and 2.475, I will only retrieve the respective value related to 2.445 -- how would I retrieve the other value at 2.475?

Essentially, if a lookup_value rests in between two values of whatever is being referenced, I am trying to average those two closest numbers. Is there a function I've overlooked that may help with this?

Thanks...

I suppose if you can create a duplicate lookup table, only in the second one sort descending, you can set the fourth VLOOKUP parameter to -1, which I think will do what you're looking for.

VLOOKUP doesn't play as nicely, but I have a formula I use that does this, a mixing of INDEX/MATCH plus a standard lookup.

Since you're trying to use fuzzy match on a VLOOKUP, I can safely assume the data is already sorted.

In this example, the "match" value H3 is "MATCHED", and then the result is incremented by 1 if it isn't an exact match. Then value from column B is returned for the answer.

=INDEX($B$2:$B$8, MATCH(H3,$A$2:$A$8,1) + (LOOKUP(H3,$A$2:$A$8) < H3))

Hi, with Vlookup this will be tricky. But you could use Index/Match instead.

You need to sort your lookup table in descending order, then use

=INDEX(lookup_table,MATCH(lookup_value,lookup_column,-1),column)

Using the -1 as the last argument, Match will find the next greatest value, but the lookup_range must be sorted descending. Also, make sure that Match looks only in the column, don't give it the whole table.

hope that was not too convoluted...

I'm still having trouble wrapping my mind around these functions for a higher value.

If I have the following:

A / B / C / D
2.445 / 312 / 2.450 / = functions
2.470 / 345 / ---- / -----

=LOOKUP(C1,A1:B2) will give me 312, as expected.

However, the =INDEX(... suggestion doesn't seem to work. Basically, yes, I am trying to go down one extra cell to B2 and retrieve 345, at which point I can average the two.

Very new to formulas so the index/match is difficult for me to understand.

Thank you for such fast replies!

Also, in regards to descending order:

Column B is already a function referencing other cells and does not have the $ signs -- how would I be able to create a duplicate without redoing each cell?

As I said above, for the Index/Match function to work with the last Match parameter being -1, the lookup table must be in descending order.

It can take a wee while to internalise Index/Match. You're not alone

I'm getting a bit lost now. Could you possibly upload a small data sample in a workbook. That'll be a lot easier to follow.

You can upload a file by clicking "Go Advanced" below and then the paper clip icon.