Searching for a value in a table with different ranges

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
109
Office Version
  1. 2016
Platform
  1. Windows
Dear reader,

I have a table looking like this:

Min (Q)(R)Max (S)Average (T)MinRange (U)(V)MaxRange (W)
0,000-0,1490,10-313
0,150-0,2490,2314-523
0,250-0,3490,3524-732
0,350-0,4490,4733-942
0,450-0,5490,5943-1151

I've got a cell to input any value next to it. I'm trying to get the result of the average column to which any Range number applies.
Formula for the input cell, which works for the first row: =IF(OR(AA5<=U9;AA5>=W9);"No Result";T9)

So if the number lies between 0 and 313 I want to get the result 0,1. If the number lies between 314 and 523 I want to get the result 0,2 and so on.

The formula works but only for the first row. I'm not sure how to get it to search each Min/Maxrange for the right result.

Any help is much appreciated, thanks!
Steve
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
How about
Excel Formula:
=SUMIFS(T:T,U:U,"<="&AA5,W:W,">="&AA5)
 
Upvote 0
Solution
Thank you Fluff.

It's almost working perfectly except for when the number is exactly the MinRange sometimes. Like when I input 733 it's giving me the result 0, instead of 0,4. For any other numbers it's working perfectly.
 
Upvote 0
Are the values in U & W the result of formulae?
 
Upvote 0
Very probably, you could use
Excel Formula:
=SUMIFS(T:T,U:U,"<"&AA5+1,W:W,">="&AA5)
but it would probably be better to round the figures in U & W, unless you need exact values for other calculations.
 
Upvote 0
Thank you for all your help Fluff, that formula is working perfectly with my current formulae.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top