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
gh_userAsked:
Who is Participating?
 
Ryan ChongCommented:
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))

Open in new window

Example-Range-table_b.xlsx
0
 
Ryan ChongCommented:
your range definition seems wrong:

>50
20-49
10-29
<10

what if for value = 50?

what's the possible mininum and maximum value of the range?
0
 
gh_userAuthor Commented:
It was meant to be 10-19
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
gh_userAuthor Commented:
and 20-50
0
 
gh_userAuthor 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
 
Ryan ChongCommented:
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
 
gh_userAuthor Commented:
Good quick response
0
 
gh_userAuthor 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
 
Ryan ChongCommented:
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)

Open in new window


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

Open in new window


check attached for more info.
Example-Range-table_c_2.xlsm
0
 
gh_userAuthor Commented:
Thanks Ryan.  Much appreciated.
I'm not familiar with using macros so try to avoid.
I might re-post question to see if I can find other solution.
Have a great day.
Terima kasih.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.