# Looking for closer value in a table

#### vegetamaker

##### New Member
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!

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.

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 ^^

Last edited:

Code:
``````[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!

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.

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

M.

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.

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. ^^

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.

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.

Replies
3
Views
227
Replies
3
Views
298
Replies
6
Views
145
Replies
5
Views
190
Replies
3
Views
222

1,196,132
Messages
6,013,635
Members
441,777
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back