excel - FInd nearest value within list condition

Question

I want to find nearest value of a cell but don't know how.

In my excel sheet cell B1 has a value(LIST NAME) and cell B2 has other value which is to be searched with condition. If cell B1 has value GP_42(list name) then search the value of cell B2 withing list GP_42 (D4:D13) If cell B1 has value GP_42(list name) then se[![enter image description here][2]][2]arch the value of cell B2 withing list GP_42 (E4:E13). If value doesn't match then result should be the nearest matched value. Result should be display in the cell B3.

Answer 1

I'll assume you want the closest value. This means that you need the absolute (ABS function) of the difference between the value in GP_42 and your search value.

In B3 as an array formula1,

=INDEX(GP_42, AGGREGATE(15, 6, ROW(GP_42)/(ABS(GP_42-B$2)=MIN(ABS(GP_42-B$2))), ROW(1:1))-ROW(GP_42)+1)

I have used ROW(1:1) to represent the number 1. This gives you the first encountered match. In my expanded examples, the third has two matches that meet the 'minimum difference' in B9 and B10. B10 is achieved by filling down. This advances ROW(1:1) to ROW(2:2) which represents 2 and gives you the second available match.

---

1 _{Array formulas need to be finalized with Ctrl+Shift+Enter?. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}