+ Reply to Thread
Results 1 to 27 of 27

Function for more than 1 criteria

  1. #1
    Registered User
    Join Date
    02-14-2005
    Posts
    69

    Exclamation Function for more than 1 criteria

    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...

  2. #2
    Anne Troy
    Guest

    Re: Function for more than 1 criteria

    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




    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:

    http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  3. #3
    Biff
    Guest

    Re: Function for more than 1 criteria

    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

    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:
    > http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  4. #4
    Anne Troy
    Guest

    Re: Function for more than 1 criteria

    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




    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:

    http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  5. #5
    Biff
    Guest

    Re: Function for more than 1 criteria

    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

    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:
    > http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  6. #6
    Anne Troy
    Guest

    Re: Function for more than 1 criteria

    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




    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:

    http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  7. #7
    Biff
    Guest

    Re: Function for more than 1 criteria

    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

    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:
    > http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  8. #8
    Biff
    Guest

    Re: Function for more than 1 criteria

    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

    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:
    > http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  9. #9
    Anne Troy
    Guest

    Re: Function for more than 1 criteria

    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




    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:

    http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  10. #10
    Anne Troy
    Guest

    Re: Function for more than 1 criteria

    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




    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:

    http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  11. #11
    Biff
    Guest

    Re: Function for more than 1 criteria

    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

    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:
    > http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  12. #12
    Anne Troy
    Guest

    Re: Function for more than 1 criteria

    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




    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:

    http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  13. #13
    Biff
    Guest

    Re: Function for more than 1 criteria

    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

    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:
    > http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  14. #14
    Anne Troy
    Guest

    Re: Function for more than 1 criteria

    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




    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:

    http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  15. #15
    Biff
    Guest

    Re: Function for more than 1 criteria

    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

    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:
    > http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  16. #16
    Biff
    Guest

    Re: Function for more than 1 criteria

    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

    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:
    > http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  17. #17
    Anne Troy
    Guest

    Re: Function for more than 1 criteria

    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




    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:

    http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  18. #18
    Biff
    Guest

    Re: Function for more than 1 criteria

    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

    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:
    > http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  19. #19
    Anne Troy
    Guest

    Re: Function for more than 1 criteria

    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




    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:

    http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  20. #20
    Anne Troy
    Guest

    Re: Function for more than 1 criteria

    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




    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:

    http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  21. #21
    Biff
    Guest

    Re: Function for more than 1 criteria

    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

    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:
    > http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  22. #22
    Anne Troy
    Guest

    Re: Function for more than 1 criteria

    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




    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:

    http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  23. #23
    Biff
    Guest

    Re: Function for more than 1 criteria

    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

    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:
    > http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  24. #24
    Anne Troy
    Guest

    Re: Function for more than 1 criteria

    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




    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:

    http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  25. #25
    Biff
    Guest

    Re: Function for more than 1 criteria

    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

    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:
    > http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  26. #26
    Anne Troy
    Guest

    Re: Function for more than 1 criteria

    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




    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:

    http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




  27. #27
    Biff
    Guest

    Re: Function for more than 1 criteria

    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

    "rhani111" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...
    >
    >
    > --
    > rhani111
    > ------------------------------------------------------------------------
    > rhani111's Profile:
    > http://www.excelforum.com/member.php...o&userid=19940
    > View this thread: http://www.excelforum.com/showthread...hreadid=386331
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1