Formula to find 3 closest values in a column when given one value

[Brennen26]

Hey guys,

I am trying to put together a formula to find the 3 best matches when I input a value into a cell.

I would like to put a rotor OD value in a cell then have it find the three best matching stator ID's with a tolerance of +0.010 and -0.005.

What kind of formula would I need and how could I put it together? I have attached a spreadsheet of a sample of what I'm trying to put together.

[AlKey]

Not sure if this is what you want as your description provides minimum information.
Please see attached file.

[ThirtyTwo]

Hey Brennen

With the example value you entered, 4.007
I got the following answer Z7D1071866, Z7D885926, Z7D885923

Here's the steps I took

Column F
- used to test whether each Stator is within the tolerances you stated

Column G
- used to rank all the Stators that are within range

Column C
- used to display 1,2 or 3 for the best 3 matches
- also needed to be left of the Stator S.N. for lookup function

J3
- used to display the top 3 S.N.'s

Please Login or Register  to view this content.

( you may wish to separate these into individual columns )

This method can only work if you are testing values one by one, this method cannot work if you wanted to list a number of values you want to test

[ThirtyTwo]

I think I was getting mixed up with Rotor's and Stator's, so it may not have been looking at the correct list

If my method is close to what you was originally look for I'll make the necessary changes

[ThirtyTwo]

Corrected an error in one of the formulas used for ranking

[samba_ravi]

See the attachement