Data Selection Based on Closest Value [20% Tolerance]

A

Anonnymouse

New Member
Joined
Jun 28, 2023
Messages
27
Office Version
  1. 365
Platform
  1. Windows
Hello,

In column C:G I have the original data set and in Column H:M i have a data set of measured values.

I am wondering if its possible to get in column N the closest values from H:M that are similar 20% to the original data set C:G.

Example: In Column C8 and C9 we have Identifier 9110283 with Sub Ident (D8) 1 and Sub Ident (D9) 2, I want it to look in Column H for the Identifer that matches then based on a 20% tolerance pick what ever values match closer to either sub-ident 1 or sub ident 2 in the corresponding cell (L8 and L9) on column N

=LET(r,FILTER($E$2:$G$9000,($C$2:$C$9000=L2)*($D$2:$D$9000=M2)),v_1,INDEX(r,1),v_2,INDEX(r,2),v_3,INDEX(r,3),ROWS(FILTER($H$2:$H$9000,($H$2:$H$9000=L2)*($I$2:$I$9000/v_1<(1+$O$2))*($J$2:$J$9000/v_2<(1+$O$2))*($K$2:$K$9000/v_3<(1+$O$2))*(v_1/$I$2:$I$9000<(1+$O$2))*(v_2/$J$2:$J$9000<(1+$O$2))*(v_3/$K$2:$K$9000<(1+$O$2)))))

I was using the formula above in a smaller data sample but when trying to use it with more data it gave me a #DIV/0 or a #CALC. I am wondering if someone can tell me what I am doing wrong?





TestFileV2.xlsx
CDEFGHIJKLMNO
1IdentifierSub-IdentValue 1Value 2Value 3IdentifierValue 1Value 2Value 3Item NumberSub-IdentClosestTolerance
281256931375335260812563762434525481256931#DIV/0!20%
381256932375260335812594538634226081256932#DIV/0!
481259241375335260812594538634525781259241#DIV/0!
581259243375335260812594538633725881259243#DIV/0!
681259451375335260812594538434026181259451#DIV/0!
781259452375260335812594538933526081259452#DIV/0!
891102831400335230812594539633725891102831#DIV/0!
991102832380330230812594538635026091102832#DIV/0!
1091297111365185105812594538433725991297111#DIV/0!
1191297114370195110812594538433725891297114#DIV/0!
1291377301465200145910308251826527591377301#DIV/0!
1391377302464200140910308251426527591377302#DIV/0!
1491551821345235220910308251626727691551821#DIV/0!
1591551822335225204910375551124028691551822#DIV/0!
1691618681360185200910375550924029691618681#DIV/0!
1791618682365190214910614436018210591618682#DIV/0!
1891669011400300245910889036730512791669011#DIV/0!
1991669012380240283910898148028022291669012#DIV/0!
2091669151380235235910898147328221791669151#DIV/0!
2191669152380240252911028341033224691669152#DIV/0!
2291669153380240252911028340633225191669153#DIV/0!
2393523741395295235911028339833225493523741#DIV/0!
2493523742370340213911028340333025293523742#DIV/0!
2593523811610408247911028340333225393523811#DIV/0!
2693523812520320351911028340633025493523812#DIV/0!
2793568191500325245911028340833224693568191#DIV/0!
2893568192510340234911028340133524993568192#DIV/0!
2993696801500405300911028340833224793696801#DIV/0!
3093696802499345306911028340133223993696802#DIV/0!
3193696941500405300911028341533024793696941#DIV/0!
3293696942505345305911177644433020593696942#DIV/0!
3393773361380335214911177643933520493773361#DIV/0!
3493773362398285235911177646830720493773362#DIV/0!
359738429141534070911177644233020997384291#DIV/0!
369738429241033869911178343730030297384292#DIV/0!
37550516738148019519091117834343052935505167381#DIV/0!
38550516738248019518591117834423003005505167382#DIV/0!
39550599392137028013591117834323023025505993921#DIV/0!
40550599392237028013591117834392973085505993922#DIV/0!
41550599392338229513891117834613052785505993923#DIV/0!
42550615183137020427591117834633052815506151831#DIV/0!
43550615183240519527791117834513102795506151832#DIV/0!
44550736706138520017091117834703152675507367061#DIV/0!
45550736706231620119491117834543122705507367062#DIV/0!
46550961278136018520091117834543202735509612781#DIV/0!
47550961278236519021491117834443152535509612782#DIV/0!
48551212312160029017591117834543102575512123121#DIV/0!
49551212312260029017691117834443252515512123122#DIV/0!
50551769192137020427591117834563072655517691921#DIV/0!
51551769192240519527791117834683122655517691922#DIV/0!
52552177984134024424491117834563172585521779841#DIV/0!
53552177984234024424491117834583072635521779842#DIV/0!
54552541351147032517091117834783272625525413511#DIV/0!
55552541351248534017191117935742322025525413512#DIV/0!
56552541351346537521591117935742352015525413513#DIV/0!
57552541351446532518391117935762372015525413514#DIV/0!
5855365165413903159291117935812352005536516541#DIV/0!
59553651654239031510891117935592521955536516542#DIV/0!
60554293254134017520091121853431852055542932541#DIV/0!
61554293254234117719891146304012752215542932542#DIV/0!
62554392455136018520091146303942752215543924551#DIV/0!
63554392455336519022091146303912722215543924553#DIV/0!
64555181438130520316691146303982722215551814381#DIV/0!
65555181438230420513491146303962752215551814382#DIV/0!
66555647464136020417591146303942722215556474641#DIV/0!
67555647464236017021091146303942722215556474642#DIV/0!
68555647470136020417591158584662873165556474701#DIV/0!
69555647470236017021091158725043072655556474702#DIV/0!
70555829397140526522091158723791851515558293971#DIV/0!
71555829397240526520091164114992552385558293972#DIV/0!
72555829915131021022091164115042472385558299151#DIV/0!
73555829915231021024091164115022522385558299152#DIV/0!
74555831978141031026591164115022552385558319781#DIV/0!
75555831978241231724691164114992472385558319782#DIV/0!
76555911808140827027491164115042472395559118081#DIV/0!
77555911808240827525591167075502452075559118082#DIV/0!
78556000498128016522091172653462302285560004981#DIV/0!
79556000498227517517591172653432502125560004982#DIV/0!
80556032108145532520091178184182921565560321081#DIV/0!
81556032108345532520091178184372851515560321083#DIV/0!
82556098622147032027091178184512801505560986221#DIV/0!
83556098622245531527591178184422801485560986222#DIV/0!
84557221669132025322591178184442801485572216691#DIV/0!
8555722166923202532291178184442851515572216692#DIV/0!
86563771634128522515591178184442851515637716341#DIV/0!
87563771634228016022091178184222951505637716342#DIV/0!
88563771638128522515591178184372901485637716381#DIV/0!
89563771638228016022091196534583121075637716382#DIV/0!
90563771639128522515591229975092502725637716391#DIV/0!
91563771639228016022091229974992502715637716392#DIV/0!
92564028298140527529091260135043102635640282981#DIV/0!
93564028298241026032091260135333052635640282982#DIV/0!
949138278384242164
959138839394312127
969138839410312124
979138839398315126
989138839454315109
9991388393982110
1009138897602332235
1019142132480302279
1029142132521300276
1039142132468317264
1049142132482320251
1059142132478312246
1069142132494327258
1079142132490325258
1089142219403205236
1099142219401200237
1109142219410205241
1119142394451245363
1129142394449240364
1139142394456242362
1149142394439262350
1159155182353232233
1169155182377240220
1179156093389265172
118915645046331596
119915645044631596
1209156848362182105
1219156848360182105
1229156848355182105
1239156848355182105
1249156848355182105
1259156848358182105
Sheet8
Cell Formulas
RangeFormula
N2:N93N2=LET(r,FILTER($E$2:$G$9000,($C$2:$C$9000=L2)*($D$2:$D$9000=M2)),v_1,INDEX(r,1),v_2,INDEX(r,2),v_3,INDEX(r,3),ROWS(FILTER($H$2:$H$9000,($H$2:$H$9000=L2)*($I$2:$I$9000/v_1<(1+$O$2))*($J$2:$J$9000/v_2<(1+$O$2))*($K$2:$K$9000/v_3<(1+$O$2))*(v_1/$I$2:$I$9000<(1+$O$2))*(v_2/$J$2:$J$9000<(1+$O$2))*(v_3/$K$2:$K$9000<(1+$O$2)))))
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Not totally clear, but I think this is what you're trying to do?

This example returns two matches where all three measured values are within 20% of the original.

ABCDEFGHIJKLMNOPQ
1OriginalMeasuredResults
2IdentifierSub-IdentValue 1Value 2Value 3IdentifierSub-IdentValue 1Value 2Value 3
38125693137533526081256931624345254386345257
4xx386342260389335260
581256931386345257
6xx386337258
781256932384340261
881256931389335260
9xx396337258
1081256933386350260
11xx384337259
12xx384337258
13
Sheet1
Cell Formulas
RangeFormula
O3:Q4O3=LET(M,K$3:M$12,FILTER(M,(I$3:I$12=C3)*(J$3:J$12=D3)*BYROW(ABS(M/E3:G3-1)<20%,LAMBDA(r,SUM(--r)=3)),"n/a"))
Dynamic array formulas.

You could wrap in TOROW() to confine the results to a single line, so that you could copy the formula down.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Cubist
  • Solved
Taking the average of the last 3,6,12 values.
Replies
5
Views
341
Fluff
Fluff
F
  • Question
Scoresheet creation for a trivia game
Replies
1
Views
274
Rijnsent
Rijnsent
M
  • Solved
#DIV/0! error
Replies
3
Views
101
Micron
M
F
  • Solved
Autofill formula based on another column (VBA)
Replies
4
Views
233
feni1388
F
JenniferMurphy
  • Question
How to adjust (correct) Z Scores for missing values?
Replies
5
Views
356
Cubist
Cubist

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,398
Latest member
rjsteward

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top