+ Reply to Thread
Results 1 to 11 of 11

INDEX MATCH IF (then list alphabetically)

  1. #1
    Registered User
    Join Date
    10-15-2018
    Location
    London, England
    MS-Off Ver
    210
    Posts
    43

    INDEX MATCH IF (then list alphabetically)

    Hi All,

    I need a formula to INDEX MATCH with an if statement to then return the results in alphabetical order

    Therefore, if my listed data (task names) is in range A3:A500 and if there is a value (I.e. <>"") in B3:B500 (task frequency) then I need in D3:D500 to return the (task names) from A3:A500 in alphabetical order

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,279

    Re: INDEX MATCH IF (then list alphabetically)

    post a sample sheet ( see yellow banner)
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  3. #3
    Registered User
    Join Date
    10-15-2018
    Location
    London, England
    MS-Off Ver
    210
    Posts
    43

    Re: INDEX MATCH IF (then list alphabetically)

    See attached example sheet, with values manually entered in column D to show expected results
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    Re: INDEX MATCH IF (then list alphabetically)

    Please try pasting the following into cell D3 and copying down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

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

    Re: INDEX MATCH IF (then list alphabetically)

    Hi,

    In D3:

    =IF(ROWS(A$3:A3)>COUNTIF(B$3:B$9,"<>"),"",LOOKUP(1,0/FREQUENCY(ROWS(A$3:A3),(B$3:B$9<>"")*COUNTIFS(A$3:A$9,"<="&A$3:A$9,B$3:B$9,"<>")),A$3:A$9))

    If A10 is always empty then this can be abbreviated to:

    =T(LOOKUP(1,0/FREQUENCY(ROWS(A$3:A3),(B$3:B$9<>"")*COUNTIFS(A$3:A$9,"<="&A$3:A$9,B$3:B$9,"<>")),A$3:A$9))


    Regards
    Click * below if this answer helped

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

  6. #6
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,065

    Re: INDEX MATCH IF (then list alphabetically)

    In E3 and down:

    =SUMIF(A3:A9,D3,B3:B9)

  7. #7
    Registered User
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    345

    Re: INDEX MATCH IF (then list alphabetically)

    D3 cell , array formula , Drag down and accross

    HTML Code: 
    =INDEX(A:A,SMALL(IF($B$3:$B$9<>"",ROW($3:$9),4^8),ROW(1:1)))&""

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,477

    Re: INDEX MATCH IF (then list alphabetically)

    This establish an unique list in alphabet order
    In D3:

    Please Login or Register  to view this content.
    **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).

    In E3
    Please Login or Register  to view this content.
    Drag all down
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-15-2018
    Location
    London, England
    MS-Off Ver
    210
    Posts
    43

    Re: INDEX MATCH IF (then list alphabetically)

    @JeteMC - Sorry for the late reply, your suggestion worked perfectly. Thank you

    @XOR LX - Sorry for the late reply, your suggestion worked perfectly. Thank you

    @wk9128- Sorry for the late reply, your suggestion worked perfectly. Thank you

    @bebo021999- Sorry for the late reply, your suggestion worked perfectly. Thank you

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    Re: INDEX MATCH IF (then list alphabetically)

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

  11. #11
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,065

    Re: INDEX MATCH IF (then list alphabetically)

    And thank you for ignoring me (just kidding.....)

+ 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] Create List - Index Match or Index Aggregate or other?
    By bambamclint in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-10-2019, 01:30 PM
  2. Replies: 8
    Last Post: 09-30-2018, 12:53 PM
  3. [SOLVED] Index match show all matches from list (only showing the first match...)
    By jakeh2475 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-24-2018, 02:11 PM
  4. [SOLVED] Index/match pulling correctly except for 1st team alphabetically
    By BuckeyeKaptn in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-22-2018, 03:18 PM
  5. INDEX MATCH MATCH doesn't work well in a Filtered List?
    By exceln3wb in forum Excel General
    Replies: 1
    Last Post: 10-05-2017, 06:57 AM
  6. Replies: 5
    Last Post: 02-18-2017, 11:21 AM
  7. Replies: 3
    Last Post: 05-19-2014, 02:01 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