+ Reply to Thread
Results 1 to 8 of 8

INDEX MATCH SMALL avoid duplicates

  1. #1
    Registered User
    Join Date
    08-23-2022
    Location
    Australia
    MS-Off Ver
    365
    Posts
    29

    INDEX MATCH SMALL avoid duplicates

    Hello, I'm trying to grab context from column A from looking at values in column K but the problem is column K has some duplicate values but column A will have different context. For example

    For example column A and K:
    A K
    ABS 10
    SBS 20
    CDC 50
    SED 50
    SDD 40
    SOM 60
    EEE 70

    Column T will pick up the value in column K. Forumla in column T =IF(SMALL(K$1:K$104,ROW(K1))<>0,SMALL(K$1:K$104,ROW(K1)),"")
    T
    20
    40
    50
    50
    60
    70

    And in column N or any other as an example will pick up A based on column T. As you can see below it's returning CDC twice when it should be SED. Please help?

    =IF(T2<>"",INDEX(A$1:K$104,MATCH(SMALL(K$1:K$104,ROW(K1)),K$1:K$104,0),1),"")

    N T
    SBS 20
    SDD 40
    CDC 50
    CDC 50
    SOM 60
    #NUM! 70

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,260

    Re: INDEX MATCH SMALL avoid duplicates

    You haven't really told us what you want to see. So a guess:

    =SORTBY(A3:B9,B3:B9)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Registered User
    Join Date
    08-23-2022
    Location
    Australia
    MS-Off Ver
    365
    Posts
    29

    Re: INDEX MATCH SMALL avoid duplicates

    Thank you so much for responding so quickly Glenn! Sorry to be clear, in column A and K can be quite random places because in column T I need to pick up value in column K from most negative to positive amounts first and would like to pick up the names in column A but because the amounts in column T is the same, it's returning the same name. Please see attached. So basically in N10, I would like to see "SOM" I hope I make sense.
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,260

    Re: INDEX MATCH SMALL avoid duplicates

    That's clearer!

    =LET(a,A1:A60,k,K1:K60,code,FILTER(a,k<>""),amt,FILTER(k,K1:k<>""),SORTBY(CHOOSE({1,2},code,amt),amt))
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-23-2022
    Location
    Australia
    MS-Off Ver
    365
    Posts
    29

    Re: INDEX MATCH SMALL avoid duplicates

    wow that's amazing Glenn! Thank you so much exactly what I needed but I'm so sorry I tried to put the formulas in (still a beginner) and I get #SPILL. Would love to know how you did that

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,260

    Re: INDEX MATCH SMALL avoid duplicates

    If there are values in the cells where the formula will produce results, it will give a spill error. So delete whatever is in those cells first.

  7. #7
    Registered User
    Join Date
    08-23-2022
    Location
    Australia
    MS-Off Ver
    365
    Posts
    29

    Re: INDEX MATCH SMALL avoid duplicates

    This is wonderful thank you very much Glenn! Works perfectly

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,260

    Re: INDEX MATCH SMALL avoid duplicates

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

+ 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] INDEX MATCH SMALL but ignore duplicates
    By pareid in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 07-30-2019, 12:18 PM
  2. [SOLVED] Index Match Match for a small table lookup
    By slvrbktom in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 09-05-2018, 06:08 PM
  3. [SOLVED] Combining INDEX/MATCH with several criteria and INDEX/SMALL
    By Dresas in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-15-2018, 11:02 AM
  4. [SOLVED] Avoid #REF in VLOOKUP or INDEX MATCH?
    By NewYears1978 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-22-2017, 02:27 PM
  5. [SOLVED] tie scores (stopping/skipping duplicates) using index/match plus small function
    By Mrjpjones in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-06-2016, 02:13 PM
  6. Match Index, Small Index with Concatenate
    By kharding15 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-30-2015, 05:38 PM
  7. [SOLVED] Index/Match - how to avoid same lookup if there are duplicates?
    By consulttk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2012, 02:40 PM

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