Find nearest and exclude member of array

[shvraka]

Hello!
I need help with {=INDEX(MATCH.....} or something like that formula.

I would like to make a formula that finds the nearest value, of a given cell, in an array of data, and exclude that array member in folowing cell.
Here is an example:

Thank you in advance!

 

[Eric W]

Try:

Book1
	A	B	C
1	array1	array2	formula for nearest in array1
2	2	15	14
3	8
4	9	4	2
5	12	1	8
6	25
7	43
8	77	25	25
9	14
Sheet2

Cell Formulas
Range	Formula
C2:C9	C2	=IF(B2="","",INDEX($A$2:$A$9,MATCH(MIN(ABS($A$2:$A$9+COUNTIF($C$1:$C1,$A$2:$A$9)*9^9-B2)),ABS($A$2:$A$9+COUNTIF($C$1:$C1,$A$2:$A$9)*9^9-B2),0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Note that this formula works from the top down. So the C4 cell calculates 2 as being closest, then the C5 cell excludes 2 as an option and picks 8 instead. Your example had the C5 value picked first. If the values in column B are entered manually in a random order, then you'd probably need to create a Worksheet Change event to perform the job of filling the C column.

 

[shvraka]

Thank you!

Works fine!

 

[Eric W]

Happy to help! Thanks for the feedback.