Looking for closer value in a table

[vegetamaker]

Hello! First at all thanks for read this. Very happy that find a community like this one with active members.

Well, lets see if you can help me, I must admit that my knowledge is very limited... And sorry for my English, isn't my primary language.

I am trying find the closer value in a table. I did it already following some guides in the net, but with a problem: it only find values in a single column and I need find the closer value in an entire table.

Here an example: Gyazo - 12ab7ae5d7df11e5638c8104b876b20f.png

The idea is find closer value in the second column (called ",0") and later the closer value in the same row (or better all in a single step, that could be better). Then I guess I can take value from first colum (called XXXX) and the name of actual column with a result of, per example, 6,7 that is the final value that I am trying find.

I tryed before other thing, doing something with only a column, but when I wanted calculate decimals it wasn't giving me enough good results.

Well, thats it! Thanks so much!

 

[Marcelo Branco]

Welcome to Mr Excel

We need to copy your data for testing purposes. An image is not useful.

So,put borders in your table; copy (Ctrl+C); and paste (Ctrl+V) in the forum reply page

Besides that, please be specific about what you mean by closer value. For example, what is the expected value if the number is 23,53? 6,1 or 6,2?

M.

 

[vegetamaker]

Sorry, I tryed edit, but didn't found the option

Well, I am trying this:

=INDEX(J26:J36; MATCH(MIN(ABS(K26:T36-U23));ABS(K26:T36-U23);0))

This works fine if you just use a column to compare the value. But if I add more columns it return #N/A

Thanks ^^

 

[vegetamaker]

Thanks for the answer!

[TABLE="width: 1122"]
<tbody>[TR]
[TD]XXXX[/TD]
[TD],0[/TD]
[TD],1[/TD]
[TD],2[/TD]
[TD],3[/TD]
[TD],4[/TD]
[TD],5[/TD]
[TD],6[/TD]
[TD],7[/TD]
[TD],8[/TD]
[TD],9[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]0,0[/TD]
[TD]0,34[/TD]
[TD]0,68[/TD]
[TD]1,03[/TD]
[TD]1,37[/TD]
[TD]1,71[/TD]
[TD]2,05[/TD]
[TD]2,40[/TD]
[TD]2,74[/TD]
[TD]3,08[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]3,4[/TD]
[TD]3,77[/TD]
[TD]4,11[/TD]
[TD]4,45[/TD]
[TD]4,79[/TD]
[TD]5,13[/TD]
[TD]5,48[/TD]
[TD]5,82[/TD]
[TD]6,16[/TD]
[TD]6,50[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]6,8[/TD]
[TD]7,41[/TD]
[TD]7,97[/TD]
[TD]8,53[/TD]
[TD]9,09[/TD]
[TD]9,65[/TD]
[TD]10,21[/TD]
[TD]10,77[/TD]
[TD]11,33[/TD]
[TD]11,89[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]12,4[/TD]
[TD]12,98[/TD]
[TD]13,51[/TD]
[TD]14,05[/TD]
[TD]14,58[/TD]
[TD]15,11[/TD]
[TD]15,65[/TD]
[TD]16,18[/TD]
[TD]16,71[/TD]
[TD]17,25[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]17,8[/TD]
[TD]18,09[/TD]
[TD]18,41[/TD]
[TD]18,72[/TD]
[TD]19,04[/TD]
[TD]19,35[/TD]
[TD]19,66[/TD]
[TD]19,98[/TD]
[TD]20,29[/TD]
[TD]20,60[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]20,9[/TD]
[TD]21,13[/TD]
[TD]21,34[/TD]
[TD]21,55[/TD]
[TD]21,76[/TD]
[TD]21,96[/TD]
[TD]22,17[/TD]
[TD]22,38[/TD]
[TD]22,59[/TD]
[TD]22,80[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]23,0[/TD]
[TD]23,36[/TD]
[TD]23,70[/TD]
[TD]24,05[/TD]
[TD]24,39[/TD]
[TD]24,74[/TD]
[TD]25,08[/TD]
[TD]25,43[/TD]
[TD]25,77[/TD]
[TD]26,12[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]26,5[/TD]
[TD]26,99[/TD]
[TD]27,52[/TD]
[TD]28,05[/TD]
[TD]28,58[/TD]
[TD]29,11[/TD]
[TD]29,64[/TD]
[TD]30,17[/TD]
[TD]30,70[/TD]
[TD]31,23[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]31,8[/TD]
[TD]32,55[/TD]
[TD]33,34[/TD]
[TD]34,14[/TD]
[TD]34,93[/TD]
[TD]35,72[/TD]
[TD]36,52[/TD]
[TD]37,31[/TD]
[TD]38,11[/TD]
[TD]38,90[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]39,7[/TD]
[TD]40,88[/TD]
[TD]42,07[/TD]
[TD]43,27[/TD]
[TD]44,46[/TD]
[TD]45,65[/TD]
[TD]46,84[/TD]
[TD]48,03[/TD]
[TD]49,22[/TD]
[TD]50,41[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]51,6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Dunno if this is what you was asking for ^^'

For 23,53 it should return 6.2, lets be good with final result. I forgot mention it, thanks!

 

[Marcelo Branco]

Maybe something like this

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

1​

XXXX​

0​

0,1​

0,2​

0,3​

0,4​

0,5​

0,6​

0,7​

0,8​

0,9​

Number​

Result​

2​

0​

0​

0,34​

0,68​

1,03​

1,37​

1,71​

2,05​

2,4​

2,74​

3,08​

23,53​

6,2​

3​

1​

3,4​

3,77​

4,11​

4,45​

4,79​

5,13​

5,48​

5,82​

6,16​

6,5​

7,40​

2,1​

4​

2​

6,8​

7,41​

7,97​

8,53​

9,09​

9,65​

10,21​

10,77​

11,33​

11,89​

15,65​

3,6​

5​

3​

12,4​

12,98​

13,51​

14,05​

14,58​

15,11​

15,65​

16,18​

16,71​

17,25​

30,18​

7,7​

6​

4​

17,8​

18,09​

18,41​

18,72​

19,04​

19,35​

19,66​

19,98​

20,29​

20,6​

20,90​

5,0​

7​

5​

20,9​

21,13​

21,34​

21,55​

21,76​

21,96​

22,17​

22,38​

22,59​

22,8​

46,70​

9,6​

8​

6​

23​

23,36​

23,7​

24,05​

24,39​

24,74​

25,08​

25,43​

25,77​

26,12​

9​

7​

26,5​

26,99​

27,52​

28,05​

28,58​

29,11​

29,64​

30,17​

30,7​

31,23​

10​

8​

31,8​

32,55​

33,34​

34,14​

34,93​

35,72​

36,52​

37,31​

38,11​

38,9​

11​

9​

39,7​

40,88​

42,07​

43,27​

44,46​

45,65​

46,84​

48,03​

49,22​

50,41​

Array formula in N2 copied down
=INDEX($A$2:$A$12,MATCH(M2,$B$2:$B$12))&","&RIGHT(INDEX($B$1:$K$1,MATCH(MIN(ABS(M2-INDEX($B$2:$K$12,MATCH(M2,$B$2:$B$12),))),ABS(M2-INDEX($B$2:$K$12,MATCH(M2,$B$2:$B$12),)),0)))

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Marcelo Branco]

Question: what is the the expected result if the number is 39,6? 9,0 or 8,9?

M.

 

[Marcelo Branco]

I think my formula above is not correct

Try this array formula in N2 copied down
=MAX(0+IF(ABS(M2-$B$2:$K$12)=MIN(ABS(M2-$B$2:$K$12)),$A$2:$A$12&","&RIGHT($B$1:$K$1)))

Ctrl+Shift+Enter

Format the result cells:
Number with 1 decimal place

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

1​

XXXX​

0​

0,1​

0,2​

0,3​

0,4​

0,5​

0,6​

0,7​

0,8​

0,9​

Number​

Result​

2​

0​

0​

0,34​

0,68​

1,03​

1,37​

1,71​

2,05​

2,4​

2,74​

3,08​

23,53​

6,2​

3​

1​

3,4​

3,77​

4,11​

4,45​

4,79​

5,13​

5,48​

5,82​

6,16​

6,5​

7,40​

2,1​

4​

2​

6,8​

7,41​

7,97​

8,53​

9,09​

9,65​

10,21​

10,77​

11,33​

11,89​

15,65​

3,6​

5​

3​

12,4​

12,98​

13,51​

14,05​

14,58​

15,11​

15,65​

16,18​

16,71​

17,25​

30,18​

7,7​

6​

4​

17,8​

18,09​

18,41​

18,72​

19,04​

19,35​

19,66​

19,98​

20,29​

20,6​

20,90​

5,0​

7​

5​

20,9​

21,13​

21,34​

21,55​

21,76​

21,96​

22,17​

22,38​

22,59​

22,8​

46,70​

9,6​

8​

6​

23​

23,36​

23,7​

24,05​

24,39​

24,74​

25,08​

25,43​

25,77​

26,12​

39,60​

9,0​

9​

7​

26,5​

26,99​

27,52​

28,05​

28,58​

29,11​

29,64​

30,17​

30,7​

31,23​

22,95​

6,0​

10​

8​

31,8​

32,55​

33,34​

34,14​

34,93​

35,72​

36,52​

37,31​

38,11​

38,9​

11​

9​

39,7​

40,88​

42,07​

43,27​

44,46​

45,65​

46,84​

48,03​

49,22​

50,41​

12​

10​

51,6​

M.

 

[vegetamaker]

I reproduced your example in my excel. Worked like a charm! I just need change coordenates to add it to my own table.

Thanks so much!!!


PD: Oh, I just found your second answer. Ill try it ^^

For 39.6 it should be 9. ^^

 

[Marcelo Branco]

I reproduced your example in my excel. Worked like a charm! I just need change coordenates to add it to my own table.

Thanks so much!!!


PD: Oh, I just found your second answer. Ill try it ^^For 39.6 it should be 9. ^^

Try the new version (post #7)

M.

 

[vegetamaker]

I tryed your new formula. Very elegant! Tomorrow Ill keep few hours trying understanding it, but for now It worked perfectly.

Just a problem. If I use 0.17 or less it give me 10.9 as result. I guess I must use a IF to skip that problem, but I can fix it in a very simple way: just adding impossible notes to 10,1 and above.

Again, thanks so much for your time.