How can i input an exact number and have excel show me results within a tolerance?

I work at a plastic extrusion company which makes small tubing. We have calibrated metal wires (aka mandrels) that we run through the inside diameter of the tubing to assure that it is consistent throughout. We have hundreds of these mandrels, various sizes, and they all have a tolerance.

That being said, we have an excel database listing the minimum and maximum diameter of each mandrel. One column has the minimum and the other has the maximum. My problem is that if i need a mandrel for a tube with an inner diameter of, say, 0.0170", it becomes time consuming to scan the worksheet to find a mandrel that satisfies 0.0170" within its min and max range.

Here is a sample of what my sheet looks like:

Mandrel / Diameter Min / Diameter Max
1............0.0116".................0.0119"
2............0.0120".................0.0122"
3............0.0121".................0.0123"
4............0.0140".................0.0142"
5............0.0140".................0.0143"
6............0.0141".................0.0143"
7............0.0143".................0.0146"
8............0.0143".................0.0145"
9............0.0143".................0.0145"
10..........0.0148".................0.0153"
11..........0.0148".................0.0150"
12..........0.0149".................0.0149"
13..........0.0149".................0.0150"
14..........0.0149".................0.0152"
15..........0.0151".................0.0155"
16..........0.0157".................0.0159"
17..........0.0157".................0.0162"
18..........0.0158''.................0.0161''
19..........0.0159".................0.0161"
20..........0.0159".................0.0160"

So if i have a tube that is supposed to have a inside diameter of 0.0142", i could use mandrel 4,5,6. I am trying to find a way to tell excel what target diameter i am looking for, and have excel respond with the various mandrels that have a tolerance including my desired diameter.

By the way you are going to need to use numbers, as in my attachment. The data you showed here looks like text.

Try

=INDEX($A$2:$A$21,MATCH($H$2,$B$2:$B$21,1)-1)

and

=INDEX($A$2:$A$21,MATCH($H$2,$B$2:$B$21,1)

which will return results 5 & 6 for your example of 0.0142

see attached file. this will capture all possible matches.

see attached file. this will capture all possible matches.

I updated the file with your numbers.

I am glad to see that my question was understood. I wasnt really sure how to explain it. Thank you for all the help and suggestions. I really like what 6StringJazzer suggested. It definitely helps!

Please don't think i am not grateful, but ideally, I would like to be able to type in the target diameter and have the worksheet filter out all rows that are not possibilities. I am only as smart as an average excel person, but some people i work with have a hard time with computers in general. Those people will be using this spreadsheet also, so i want to make it as simple and clear as possible. I'd like to tell them, all you have to do is "type in your target diameter here" and excel shows them nothing but the mandrels that can be used.

I didn't realize i could attach my spreadsheet. Ill attach it now so all you Excel wizards can work your magic.

Doesn't post #7 do what you want?

Where are they entering the target value?

It does do what i want, in a way. Id like to see the entire row though because the spreadsheet i attached in post #8 has a physical location column telling the person where to go, in the factory, to obtain the mandrel needed. I would probably have "Target" in A1 and have the value to be entered in B1.