# Excel Table. Lookup where range

I have a table in which comprises ranges of values in which I want to lookup a value a falls within a particular range.  Whats the best formula to do this.  I have attached a simple example table.
Example-Range-table.xlsx
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Business Systems Analyst , ex-Senior Application EngineerCommented:

>50
20-49
10-29
<10

what if for value = 50?

what's the possible mininum and maximum value of the range?
0
Author Commented:
It was meant to be 10-19
0
Author Commented:
and 20-50
0
Business Systems Analyst , ex-Senior Application EngineerCommented:
I guess to make it easier, try to break your range with min and max values pairing.

then apply formula like:

``````=INDEX(C\$4:C\$7,MATCH(B13,B\$4:B\$7,-1))
``````
Example-Range-table_b.xlsx
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks Ryan.  Just clarifying...do I need the Min column, as doesnt seem to be considered in the formula?  It seems to be there just to clarify to the viewer what the range is.
0
Business Systems Analyst , ex-Senior Application EngineerCommented:
do I need the Min column, as doesnt seem to be considered in the formula?  It seems to be there just to clarify to the viewer what the range is.
yes, in this case, the min is more for indication of the range.

you can remove it when necessary.
0
Author Commented:
Good quick response
0
Author Commented:
Hi Ryan.  Just realised I will need formula for two range criteria.
I know you have solved my initial query but are you able to help with this also?
Have attached a revised table showing 2 criteria, being no. of people and duration.  I have only shown the 'max' column to keep simple.
Example-Range-table_c.xlsx
0
Business Systems Analyst , ex-Senior Application EngineerCommented:
don't have much time to think for a better solution but come out with a Macro function instead:

``````=CompareRange(Range1,Value1,Range2,Value2,RangeMapped)
``````

``````Function CompareRange(ByVal range1 As Range, ByVal value1 As Double, ByVal range2 As Range, ByVal value2 As Double, ByVal rangeMap As Range) As String
Dim r As Range
For Each r In range1
If r.Value >= value1 Then
If Cells(r.Row, range2.Column) >= value2 Then
CompareRange = Cells(r.Row, rangeMap.Column)
End If
End If
Next
End Function
``````