Hello everyone,

I'm trying to find the closest match of a given value (always in the same cell but the value change) in a selected range (changing according to the value of another cell).

Here's an example:

1- I ask for a Drive RPM (869 RPM)
2- A formula check for the closest equal or higher match (870RPM) in the entire range (Sheet2!J2:J564)
3- According to the RPM (870RPM) it gives me the matching HP per belt (1.53, 2.30, 3.05 & 3.79). In this case there is 4 options.
4- I would then like to have the correction factor based on the length of the belt (inputed in a cell) and the different HP per belt.


I need something similar to the following function but I would need to put variables instead of fixed cells:

=IF(CT18>MAX(Sheet2!J2:J564);"Error";INDEX(Sheet2!J2:J564;MATCH(LARGE(Sheet2!J2:J564;COUNTIF(Sheet2!J2:J564;">="&CT18));Sheet2!J2:J564;0)))



Something like that:

=IF(CT18>MAX(Sheet2!Cells(2,x):Cells(564,x);"Error";INDEX(Sheet2!Cells(2,x):Cells(564,x);MATCH(LARGE(Sheet2!Cells(2,x):Cells(564,x);COUNTIF(Sheet2!Cells(2,x):Cells(564,x);">="&CT18));Sheet2!Cells(2,x):Cells(564,x);0)))

With "x" as a variable previously defined



Thanks in advance