vlookup with 5% range?

[hlumbard]

I've read the other threads on this but not finding what I need.

I have a list of dimensions in A and corresponding parts numbers in B. I want to enter some value in a cell and return the part number that within +-5% of the dimension. Is there anyway to do this?

Obviously this is not working but can I place some sort of condition on the TRUE/FLASE at the end?

=VLOOKUP(A22,$A$1:$B$13,2,0)

 

[MrKowz]

Welcome to the forums!

Can you please provide some data, along with your input(s) and desired output? I have a feeling this is going to require an array-entered index/match.

 

[hlumbard]

OK, not sure the best way to do this but here goes. (This is just an example of a much longer list I have to do)

Column A has various values between 0-15
Column B has the part number associated with those.

I want to have a cell (A22, or any unused cell really) where I enter a value somewhere between 0-15 and in the cell next to it (where this vlookup formula is) displays the closest match +-5% (because I might not have a part number that exactly matches what I've entered.)

 

[MrKowz]

When you say +/- 5%, do you want it +/- 5% of the number searched, or +/- 0.05.

Ie, if you search for 10.5, do you want it to find a match between 9.075 (10.5*(1-0.05)) and 11.025 (10.5*(1+0.05)), or a match between 10.45 (10.50 - 0.05) and 10.55 (10.50 + 0.05).

 

[hlumbard]

The first +- 5% of number searched.
9.075 (10.5*(1-0.05)) and 11.025 (10.5*(1+0.05))

 

[MrKowz]

See if any of these formulas do what you are needing.
Note: I used 0.050000000001 instead of 0.05 because if a part was exactly 0.05 above the target value, it would not find a match due to Excel's floating point decimal issue.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Dimensions</td><td style=";">Part</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">0.75</td><td style=";">Item1</td><td style="text-align: right;;"></td><td style=";">Dimension:</td><td style="text-align: right;;">10.45</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1.5</td><td style=";">Item2</td><td style="text-align: right;;"></td><td style=";">Exact Match</td><td style=";">Item17</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1.75</td><td style=";">Item3</td><td style="text-align: right;;"></td><td style=";">First Closest Match:</td><td style=";">Item16</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">3</td><td style=";">Item4</td><td style="text-align: right;;"></td><td style=";">Last Closest Match:</td><td style=";">Item18</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">3.9</td><td style=";">Item5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">5.1</td><td style=";">Item6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">5.25</td><td style=";">Item7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">5.4</td><td style=";">Item8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">7.2</td><td style=";">Item9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">8.4</td><td style=";">Item10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">8.7</td><td style=";">Item11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">9.15</td><td style=";">Item12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">9.25</td><td style=";">Item13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">9.6</td><td style=";">Item14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">10.1</td><td style=";">Item15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">10.4</td><td style=";">Item16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">10.45</td><td style=";">Item17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">10.5</td><td style=";">Item18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">12.45</td><td style=";">Item19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">12.75</td><td style=";">Item20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=INDEX(<font color="Blue">B2:B21,MATCH(<font color="Red">E2,A2:A21,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">{=IF(<font color="Blue">ISERROR(<font color="Red">INDEX(<font color="Green">B2:B21,MATCH(<font color="Purple">1,IF(<font color="Teal">ABS(<font color="#FF00FF">A2:A21-E2</font>)<=0.050000000001,1,""</font>),0</font>)</font>)</font>),"No part within 5%",INDEX(<font color="Red">B2:B21,MATCH(<font color="Green">1,IF(<font color="Purple">ABS(<font color="Teal">A2:A21-E2</font>)<=0.050000000001,1,""</font>),0</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">{=IF(<font color="Blue">ISERROR(<font color="Red">INDEX(<font color="Green">B2:B21,MATCH(<font color="Purple">9.99E+307,IF(<font color="Teal">ABS(<font color="#FF00FF">A2:A21-E2</font>)<=0.050000000001,1,""</font>)</font>)</font>)</font>),"No part within 5%",INDEX(<font color="Red">B2:B21,MATCH(<font color="Green">9.99E+307,IF(<font color="Purple">ABS(<font color="Teal">A2:A21-E2</font>)<=0.050000000001,1,""</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[hlumbard]

I can get that to work but only when it matches exactly. (ie E4 and E5 aren't working) Hmmm.
It is giving me results though when it's 0.05 more or less than a number. Can I chnge this to %??


Also, how did you insert the sheet like that?

 

[MrKowz]

Did you confirm entry with CTRL+SHIFT+ENTER? If you did, the brackets will appear automatically (you don't manually enter the brackets).

As far as how to post the table on the board, check out the Mr. Excel HTML Maker

Edit: For some reason I thought I read you wanted it within 0.05 of the number (option #2). Let me rework it.

 

[MrKowz]

Try this:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Dimensions</td><td style=";">Part</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">0.75</td><td style=";">Item1</td><td style="text-align: right;;"></td><td style=";">Dimension:</td><td style="text-align: right;;">5.2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1.5</td><td style=";">Item2</td><td style="text-align: right;;"></td><td style=";">Exact Match</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1.75</td><td style=";">Item3</td><td style="text-align: right;;"></td><td style=";">First Closest Match:</td><td style=";">Item6</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">3</td><td style=";">Item4</td><td style="text-align: right;;"></td><td style=";">Last Closest Match:</td><td style=";">Item8</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">3.9</td><td style=";">Item5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">5.1</td><td style=";">Item6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">5.25</td><td style=";">Item7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">5.4</td><td style=";">Item8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">7.2</td><td style=";">Item9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">8.4</td><td style=";">Item10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">8.7</td><td style=";">Item11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">9.15</td><td style=";">Item12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">9.25</td><td style=";">Item13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">9.6</td><td style=";">Item14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">10.1</td><td style=";">Item15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">10.4</td><td style=";">Item16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">10.45</td><td style=";">Item17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">10.5</td><td style=";">Item18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">12.45</td><td style=";">Item19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">12.75</td><td style=";">Item20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=INDEX(<font color="Blue">B2:B21,MATCH(<font color="Red">E2,A2:A21,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">{=IF(<font color="Blue">ISERROR(<font color="Red">INDEX(<font color="Green">B2:B21,MATCH(<font color="Purple">1,IF(<font color="Teal">ABS(<font color="#FF00FF">A2:A21-E2</font>)<=(<font color="#FF00FF">E2*0.05</font>),1,""</font>),0</font>)</font>)</font>),"No part within 5%",INDEX(<font color="Red">B2:B21,MATCH(<font color="Green">1,IF(<font color="Purple">ABS(<font color="Teal">A2:A21-E2</font>)<=(<font color="Teal">E2*0.05</font>),1,""</font>),0</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">{=IF(<font color="Blue">ISERROR(<font color="Red">INDEX(<font color="Green">B2:B21,MATCH(<font color="Purple">9.99E+307,IF(<font color="Teal">ABS(<font color="#FF00FF">A2:A21-E2</font>)<=(<font color="#FF00FF">E2*0.05</font>),1,""</font>)</font>)</font>)</font>),"No part within 5%",INDEX(<font color="Red">B2:B21,MATCH(<font color="Green">9.99E+307,IF(<font color="Purple">ABS(<font color="Teal">A2:A21-E2</font>)<=(<font color="Teal">E2*0.05</font>),1,""</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[hlumbard]

You've got a new user for this site now for sure! Thank you so much!!