Test to see if user input value exists in predefined multiple ranges

Hello All,

Any help with this would be greatly appreciated. I am looking for an elegant solution. In "Sheet A", the user will input a value for a diameter. Reference data is stored in "Sheet B". "Sheet B" contains Min, Nom, and Max values of different diameters. There are about 75 products with different Min and Max diameter values. These diameters may overlap in size. If the user input value is in any of the provided ranges, I would like it to return each of the part numbers assocaited with the ranges.

For example, if the user enters a diameter of 1.125, then I would like it to test all the Min, Nom, Max values to see if it falls in that range. Thus it should test B3:D3 and B4:D4 and B5:D5... ect.

Your help is greatly appreciated. TEST.xlsx

The attached file does this for you - just enter a diameter in the bright yellow cell, and up to 5 matching part numbers will be shown in the pale yellow cells.

Hope this helps.

Pete

Pete,

This helps big time. Thank you very much. Could you explain your logic to me a little? I would really like to learn your thought process. Why is it limited at 5? I am sure I will have to continue working these type of things so I would really appreciate a deeper understand.

Once again, thank you very much for your help.

The comparisons are done in the Ref Data sheet, with this formula in Q3:

Formula:

Please Login or Register  to view this content.

This compares the input diameter with the minimum and maximum values on each row of your table, and increments a counter if the input value falls within those tolerances.

This formula in B6 of the Input sheet:

Formula:

Please Login or Register  to view this content.

looks for the value 1 in column Q of the Ref Data sheet and returns the part number from column A if there is a match - otherwise it returns the error message. There is a similar formula in B7, i.e.:

Formula:

Please Login or Register  to view this content.

but this is now looking for the value 2 in column Q and does not return an error message if there is not one. This formula is copied down and will look successively at a value of 3, then 4, then 5 etc. You can copy it further, but looking at your table there are not many parts which have the same diameters, so there is no point in doing so.

Hope this helps.

Pete