+ Reply to Thread
Results 1 to 2 of 2

IF then or index match w/ arguements for multiple mins in an array

  1. #1
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas City, MO
    MS-Off Ver
    Excel 2010
    Posts
    2

    IF then or index match w/ arguements for multiple mins in an array

    So I need a function that will pull back the mins, in order for carrier 1,2,3,4.

    The first is simple as I did an index/match to pull back the min of the array of carriers indexed off the carrier names. However, when I wrote out



    =IF($BB24>1,INDEX($AT$5:$AZ$5,MATCH(SMALL($AT24:$AZ24,2),$AT24:$AZ24,0)),"-")

    For carrier 2 where the second smallest value is what I want returned if gives me the same value. It's strange becase if you use the small function by itself for the 4th line, for example, small(array,1) and small(array,2) both yeild 2 as the return value. When you go to small(array, 3) it bumps up to 3 as it is the next biggest. However, when you put the small function in the index match it keeps returning the same carrier as the previous cell.

    Basically I need to be able to identify, all "least transit carriers"......once I have that pulled back I'll look at rates and go with least cost option but I need the service standard identified first.

    Please help! w VITR


    Transit Least Transit
    CNWY FXFE FXNL HMES RDWY DHRN VITR Min Transit # of Min Carrier 1 Carrier 2 Carrier 3 Carrier 4
    2 - 3 2 - - 1 1 1 VITR
    4 2 3 - 4 - - 2 1 FXFE -
    - - 3 2 4 - 1 1 1 VITR -
    - 2 3 - 3 - 2 2 2 FXFE
    2 2 3 - 4 - - 2 2 CNWY CNWY
    - 2 3 - 4 - 2 2 2 FXFE FXFE
    3 - 3 - 4 - 2 2 1 VITR -
    2 - 3 - 3 - 2 2 2 CNWY CNWY
    2 2 3 - 3 - - 2 2 CNWY CNWY
    - - 2 2 2 - 1 1 1 VITR -
    2 - 3 2 - - 2 2 3 CNWY CNWY
    2 2 3 - 3 - - 2 2 CNWY CNWY
    - - 3 2 - 2 2 2 3 HMES HMES
    - - 2 1 - 1 1 1 3 HMES HMES
    1 - - 1 - 1 2 1 3 CNWY CNWY
    3 2 3 - 4 - - 2 1 FXFE -
    - 2 3 - 3 - 3 2 1 FXFE -

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: IF then or index match w/ arguements for multiple mins in an array

    Hi lenick,

    Suggest you to upload the sample workbook to support your query. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

+ 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