Fins closest larger and smaller to and input number from another sheet and copy entire row

[Obedan]

I do not have any experience with VBA coding and I need your help to start learning.

I have two different sheets, The first one include the input from the user and should get the output as well.

The second sheet has all the data.

The user should input two variables D and W in the first sheet , The W value is fixed and the user will choose it from drop down list.

The code should find 2 rows with the same input W value and closest larger and closest smaller with respect to the value of D. Then it should copy these entire rows from sheet 2 to sheet 1.

 

[DILIPandey]

Okay... can you give one example by entering value in D & W ?


Regards,
DILIPandey

 

[Obedan]

Here is an example ,, Thank you

 

[svendiamond]

Is the second sheet always going to be in order like that? Sorted by Ascending D, then Ascending W, like it is in your example?

 

[Obedan]

Is the second sheet always going to be in order like that? Sorted by Ascending D, then Ascending W, like it is in your example?

Yes they are

 

[Obedan]

What I'm thinking of is when the user choose a value of W for example 0.5, then the matching should lookup rows from 2 to 10 only looking for closest higher and lower match of D then copy the corresponding rows.

And if W value choosed as 0.7, the search should only be from rows 12 to 22

I hope this comment could help and make the problem clear.

 

[svendiamond]

Ok here you go. Enter this into cell A8, use CTRL+SHIFT+ENTER, not just ENTER... and fill to the right all the way to F8:

=INDEX(Sheet2!A:A,MATCH($C$4,IF(Sheet2!$F:$F=$E$4,Sheet2!$B:$B),1))

Then enter this into cell A9, CTRL+SHIFT+ENTER, and fill to the right all the way to F9:

=INDEX(Sheet2!A:A,MATCH($C$4,IF(Sheet2!$F:$F=$E$4,Sheet2!$B:$B),1)+(LOOKUP($C$4,IF(Sheet2!$F:$F=$E$4,Sheet2!$B:$B))<>$C$4))

 

[DILIPandey]

Hi Obedan,

Sorry.. I had logged off for the day.

Here is the solution:-

Enter below formula for Closest smaller:-

=INDEX(Sheet2!A$1:A$22,MAX(IF((Sheet2!$B$1:$B$22<Sheet1!$C$4)*(Sheet2!$F$1:$F$22=Sheet1!$E$4),ROW(Sheet2!$A$1:$A$22),"")))

For closest larger:-

=INDEX(Sheet2!A$1:A$22,MIN(IF((Sheet2!$B$1:$B$22>Sheet1!$C$4)*(Sheet2!$F$1:$F$22=Sheet1!$E$4),ROW(Sheet2!$A$1:$A$22),"")))

Enter these formula as array formula (entered with key combination ctrl shift enter).

Regards,
DILIPandey

 

[Obedan]

Thank you very much both of you DILIPandey and Sevendiamond for your help.

It works with me.

This is really appreciated.

I have one more extra addition I would like to add.

let us assume we have W=0.5 and D=6 , then the smaller value will be D=5.5 , but there is no larger value ! in this case can we show an error to the user ?

Or something like that.

 

[DILIPandey]

Okay.. what you are getting in this case using my formula ?


Regards,
DILIPandey