Function for more than 1 criteria

  • Thread starter Thread starter rhani111
  • Start date Start date
R

rhani111

Hi All,

If I have to create a function that returns a value but needs two sets
of criteria to return the value, HOW do I do this?

For example

Location Hole Depth Hole Type
Ramp 15 22 OB
Ramp 2 28 PS
Ramp 4 60 IB


The values to return (lookup) has to match the ramp number AND depth
between 15-25 and be a specific TYPE of hole ie OB.....

how can I get the formula to "lookup" whether it matches the three
criteria, look to another table to see what the rate should be...
 
You could try something like:

=vlookup(a2&b2&c2,lookuprange,4)

However, you'd have to have a lookup range that ALSO concatenates the 3
columns, and that has to be the first column in your lookup range.
*******************
~Anne Troy

www.OfficeArticles.com
 
Hi!

You might be able to use a Sumproduct formula:

=SUMPRODUCT(--(A1:A50=ramp_number),--(B1:B50>=15),--(B1:B50<=25),--(C1:C50="OB"),Rate_to_Return)

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple criteria search 2
Unexplainable XIRR #NUM! Error 3
Calculate End Date Using Start Date and No. of Days excluding specific days. 0
DateRange Lookup no exact match, can't use lesser 4
Add Values that Match 2 Criteria Q 1
Confused on what to use ????? vlookup with different criteria 5
lookup function?? 2
IF and lookup functions combined? 3

Back
Top