+ Reply to Thread
Results 1 to 5 of 5

Excel Formula: Lookup 2nd 3rd 4th ... etc - unique matches.

  1. #1
    Forum Contributor
    Join Date
    06-24-2004
    MS-Off Ver
    Excel 2013 Pro Plus
    Posts
    122

    Excel Formula: Lookup 2nd 3rd 4th ... etc - unique matches.

    Hello friends, Hope all is well.

    Please take a look at the attachment.

    I would really appreciate it, if someone could assist me with a formula that would:
    1. Show unique lookups. i.e. shows all the possible branches.
    2. Which are under Mike.
    3. Without repetition of branch names.
    4. As you can see Mike is in column J and the branches are in column D.

    I tried a formula but it kept repeating NY in the 2nd, 3rd, etc, instead I want to see Tx in the 2nd and UT in the 3rd.

    Your kind help is truly appreciated! Thanks a lot in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Excel Formula: Lookup 2nd 3rd 4th ... etc - unique matches.

    Hi.

    Array formula** in M6:

    =INDEX($D$4:$D$24,SMALL(IF(FREQUENCY(IF($J$4:$J$24=$L6,MATCH($D$4:$D$24,$D$4:$D$24,0)),ROW($D$4:$D$24)-MIN(ROW($D$4:$D$24))+1),ROW($D$4:$D$24)-MIN(ROW($D$4:$D$24))+1),COLUMNS($A:A)))

    Copy to the right as required.

    Note that this formula does not take into account the possibility of there being any blanks in column D, since there weren't in the example you gave.

    We can add a clause to the effect that the formula returns a blank (as opposed to #NUM!) in cells beyond the expected number of returns if you like, though which clause that is might depend on the size of your real dataset (assuming that which you posted isn't, of course).

    Also, be aware that you can amend the upper row being referenced (24 here) to meet your needs, though, if you do, be sure not to make it too large (and certainly don't reference entire columns).

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Excel Formula: Lookup 2nd 3rd 4th ... etc - unique matches.

    Another approach, which doesn't involve array formulae:

    Put this formula in K4:

    =IF(J4="","",IF(COUNTIFS(J$4:J4,J4,D$4:D4,D4)=1,J4&"_"&COUNTIF(K$3:K3,J4&"_*")+1,""))

    and copy down beyond your data, to accommodate more data being added. Then you can have this formula in M6:

    =IFERROR(INDEX($D:$D,MATCH($L6&"_"&COLUMNS($M:M),$K:$K,0)),"")

    and copy this across as far as you need to. If you have other names in L7 down, then you can copy the formulae in M6:O6 down to pick up their branches.

    Hope this helps.

    Pete

  4. #4
    Forum Contributor
    Join Date
    06-24-2004
    MS-Off Ver
    Excel 2013 Pro Plus
    Posts
    122

    Re: Excel Formula: Lookup 2nd 3rd 4th ... etc - unique matches.

    Hello friends, and thank you all for the efforts and great feedback.

    =INDEX(COLD,SMALL(IF(FREQUENCY(IF(COLJ=TOP!L6,MATCH(COLD,COLD,0)),ROW(COLD)-MIN(ROW(COLD))+1),ROW(COLD)-MIN(ROW(COLD))+1),COLUMNS($A:A)))

    I made the formula above, and COLJ & COLD are named ranges that equal:

    COLD: =OFFSET(TOP!D$4,0,0,MAX(IF(ISBLANK(TOP!$D:$D),-4,ROW(TOP!$D:$D)-3)),-1)
    COLJ: =OFFSET(TOP!J$4,0,0,MAX(IF(ISBLANK(TOP!$J:$J),-4,ROW(TOP!$J:$J)-3)),-1)

    the formula at the very top is not working, it only works if I use actual ranges.

    please tell me what am I doing wrong in the top formula?

  5. #5
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Excel Formula: Lookup 2nd 3rd 4th ... etc - unique matches.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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] Formula to lookup a value in a row on a spreadsheet that matches name and date
    By jsclark in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-15-2013, 08:24 PM
  2. lookup formula to return data where column header matches
    By tim-harrison in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-06-2013, 12:58 AM
  3. [SOLVED] Formula to Lookup Multiple Matches
    By markbad311 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-28-2012, 04:51 AM
  4. Help with Macro/formula - Lookup that returns Dropdown List of matches
    By alfykunable in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-27-2012, 12:43 AM
  5. Formula to return unique matches
    By davegugg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-13-2011, 05:20 PM
  6. Formula creation lookup multiple matches
    By farmerswife1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-04-2011, 12:41 PM
  7. Add a 3rd lookup to a formula with 2 current matches
    By jrtulare185 in forum Excel General
    Replies: 2
    Last Post: 01-26-2010, 05:38 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