How to find the closest value in a range to a given lookup value?

[katmarsar]

Is there a function or combination of functions that finds the exact or closest value in a range of values when the range of values is compared to a given lookup value given the following constraints.

1. The range of values is unsorted.
2. The range of values may contain zero values.

Thanks

 

[zach9208]

I am looking for something similar. I need a combination of finding the lowest value with criteria applied. I basically need to combine the function of finding the lowest value while filtering to two criteria. Any ideas?

The formula below can identify the max of the available criteria, but I need the formula to identify the value closest to zero.

=MAX(IF(SUBTOTAL(2,OFFSET(G2,ROW(G2:G300000)-ROW(G2),0)),IF(B2:B300000='Overview #2'!C6,IF(E2:E300000='Overview #2'!B14,G2:G300000))))

 

[bsmtool]

Not working properly

changing the lookup value doesn't make any changes in results, even if i delete the same or if there is no value in lookup cell

any solutions??????????

 

[BiocideJ]

It is an array function, so make sure you are confirming with CTRL+SHIFT+ENTER after typing the formula.
If that isn't the problem, can you please post exactly what formula you are using.

In general, you probably should create a new thread so that the most possible people can view your questions. Not all users from 4 years ago are still active.

 

[bsmtool]

Thanks Bro

There was writing problem in formula.

Thanks once again