To look the most close value in a list o matrix of excel

[adelagza]

Hi, I have become obssesed to finish a little program to find the optimum ECT (Edge Crush Test) to get the best BCT (Box Compression Test), and I am stuck with this part I have enclosed an small archive in order to explain more clearly my problem. I get in a cell a number that might be close or near a set of numbers as you can see in the archive, what I want is once I have that number to pick from the table the appropiate and closest number in it, according to the program I am trying to put togheter this number which is obtained from it always wilkl be within a close range of the table shown staring with 23 and ending in 82, could anyone of you tell me how can I obtain a proper result, I trit with the functions INDEX, MATCH, MIN. ABS, COLUMNS with no success, always getting errors which I have not been able to decipher! I want to thank you in advance for your help on this proyect - AGUSTIN DE LA GARZA


COLOR ECT 19 = SEARCHED VALUE
CORR. SENCILLO 23
CORR. SENCILLO 26 ?? = THE MORE CLOSEST VALUE
CORR. SENCILLO 29
CORR. SENCILLO 32
CORR. SENCILLO 40
CORR. SENCILLO 44
CORR. SENCILLO 55
CORR. DOBLE 42
CORR. DOBLE 48
CORR. DOBLE 51
CORR. DOBLE 61
CORR. DOBLE 71
CORR. DOBLE 82


NOTE:- The SEARCHED VALUE can be any value but close to those of the matrix, but not equal to any of them, what I want is
that the result be the closest number in the matrix to the SEARCHED VALUE and to be placed in the cell which is called
THE MORE CLOSEST VALUE.

P.S. In the ilustrated case I imagine that THE MOST CLOSEST VALUE would be "23" let say that the SEARCHED VALUE would be
"28" then the answer would be "29" from the matrix which is the closest value to that number, actually That is what I want
that the formula might do!!!

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F
1	item	value			COLOR ECT	CLOSEST
2	CORR. SENCILLO	23			19	23
3	CORR. SENCILLO	26			28	29
4	CORR. SENCILLO	29
5	CORR. SENCILLO	32
6	CORR. SENCILLO	40
7	CORR. SENCILLO	44
8	CORR. SENCILLO	55
9	CORR. DOBLE	42
10	CORR. DOBLE	48
11	CORR. DOBLE	51
12	CORR. DOBLE	61
13	CORR. DOBLE	71
14	CORR. DOBLE	82
Sheet1

In F2 control+shift+enter. not just enter, and copy down:

=INDEX($B$2:$B$14,MATCH(MIN(ABS($B$2:$B$14-E2)),ABS($B$2:$B$14-E2),0))

 

[adelagza]

Aladin:
Thanks for your answer to my question, I did exactly all the steps as you did in your example, just changing the cells positions as they are in my program, and the answer I get is "#N/A" I have revised it through the fX and it goes tru very well but as soon as I accept it it appears the same "#N/A" Am I doing something wrong? I must tell you that the looked number that is the one you place on your E column it comes from other sheet through the used term "=sheet2!A1" could that be the culprit? I want to thank you
for your prompt reply, but I am still in the same place even when I see that your solution works and it should do the same on my program!!! - Thanks for your anticipated reply.

 

[Aladin Akyurek]

You need to apply control+shift+enter for the formula to work.

Control+shift+enter: Press down the control and the shift keys at the same time while you hit the enter key. When done successfully, Excel itself puts a pair of { and } around the formula in recognition.

 

[adelagza]

Aladin: I just wanted to thank you on the answer to my problem, it worked flowessly! Thanks
P.S. I just ask to learn as much as some of you guys know about Excel, I started a little late I am 86 now, but I do not loose confidence that I will learn enough to go through any problem by myself.

 

[Aladin Akyurek]

Aladin: I just wanted to thank you on the answer to my problem, it worked flowessly! Thanks

You are welcome. Thanks for the feedback.

P.S. I just ask to learn as much as some of you guys know about Excel, I started a little late I am 86 now, but I do not loose confidence that I will learn enough to go through any problem by myself.