How to "if number between 10 and 15 use cell A10, if number between 15-20 use cell A11"

[Jostensa]

Hello.

I have a question with excel.

Basically what I am trying to do is take a value that is calculated in one cell and use this cell in further calculations.

I have a calculation that will give me a value. This value is a theoretical minimum inner diameter of a pipe. I hope to make excel recognize that the number is for example between 15,6 and 19, or 19 and 25, or 25 and 31. Then I want to use this value to use a specific cell to put into a cell for further calculations.

Here is my current spreadsheet so it might be easier to see.

Here i get a calculated value in cell in E27 (in this case 21,8).

I want cell E28 to check this number, and see if it is between the numbers in N9-N16.

In the example with 21,8 that is between 19 and 25, then I want this to put the value of cell N13 (25) into cell E28.

And if number in cell E27 was between 25 and 31 use number from cell N14 and so on.

I have been playing a little with the INDEX and MATCH functions with no success.

Hopefully someone have a solution for this.

Thanks :)

[#2]

@Jostensa 

That could be

=AGGREGATE(15,6,1/($E$27<=$N$9:$N$16)*$N$9:$N$16,1)

[#3]

@Sergei Baklan 

If I enter this (changed to norwegian of course) I get the "there's a problem with this formula" error message

[#4]

@Jostensa 

Please check attached file, if you open it a formula shall be in your locale

[#5]

@Sergei Baklan 

This worked perfectly, thank you :)

[#6]

@Jostensa , you are welcome

[#7]

@Jostensa 

It will be so good when the Excel options finally come together.  In Office 365

= XLOOKUP( beregning, innvendigDiameter, innvendigDiameter, , 1 )

would do the same job.