+ Reply to Thread
Results 1 to 3 of 3

Index formula that returns only unique values based on cell value

  1. #1
    Registered User
    Join Date
    06-05-2014
    Posts
    2

    Index formula that returns only unique values based on cell value

    Hello Team!
    I am trying to do an index formula where it looks up an array based on a value I select in the drop down "c2". The value would be considered “C2” for this purpose on the first tab. Now when that is selected, I want to show the providers names that only have that value associated with them in columns f,g,h. I would repeat the formula for the next grouping of columns for each date. Each column is a shift and the 3 shifts are for one date.
    Here is the formula I have so far:

    =IFERROR(INDEX('Report Sheet'!$A$4:$AP$601,SMALL(IF('Report Sheet'!F$5:F$601=$C$2,ROW('Report Sheet'!F$5:F$601)),ROW(1:1))-3,1),"")

    This does great but will only give me the providers name if that value is in column F only. I want it to give me the providers name if the “c2” value is in any of the 3 columns for that row(F,G,H).


    The logic: If I enter NW into “c2”, I want to look up in my 2nd tab and see if NW is in one of those shifts for June 6. If it is, give me that providers name. and then repeat to find show the second providers name that has the same arguments.

    Now if we want to really make it fancy, I would love for it to label the shift after the provider name based on where the “c2” value is found.
    IE:
    Doe, John AM,PM
    Smith, Jack EVE PM

    Thanks you guys! I attached an example file.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Index formula that returns only unique values based on cell value

    Change this

    ROW(1:1) to be Rows($a$1:a2), then you can drag down and this increments your small count.

    I'll take a look at the other.
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index formula that returns only unique values based on cell value

    I didn't download your file.

    Try it like this:

    =IFERROR(INDEX('Report Sheet'!$A:$A,SMALL(IF(('Report Sheet'!F$5:F$601=$C$2)+('Report Sheet'!G$5:G$601=$C$2)+('Report Sheet'!H$5:H$601=$C$2),ROW('Report Sheet'!F$5:F$601)),ROWS(A$1:A1))),"")

    Still array entered.


    ROWS(A$1:A1) should refer to the first cell that the formula is entered in. If the formula is entered in Z27 and copied down then you should use:

    ROWS(Z$27:Z27)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] need a formula that returns unique values in a range
    By James C in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-08-2013, 02:26 PM
  2. Replies: 1
    Last Post: 10-23-2012, 12:08 AM
  3. Replies: 11
    Last Post: 11-05-2011, 12:34 PM
  4. Replies: 2
    Last Post: 07-10-2008, 05:53 AM
  5. a unique cell value returns multiple cell values from another shee
    By grflded in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-25-2005, 12:05 AM

Tags for this Thread

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