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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
gh_userAuthor Commented:
and 20-50
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ryan ChongBusiness 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))

Open in new window

Example-Range-table_b.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
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 ChongBusiness 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
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 ChongBusiness 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)

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.