+ 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
    365
    Posts
    75

    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 Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: INDEX MATCH IF (then list alphabetically)

    post a sample sheet ( see yellow banner)

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

    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 2019
    Posts
    17,428

    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 Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: INDEX MATCH IF (then list alphabetically)

    In E3 and down:

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

  7. #7
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: INDEX MATCH IF (then list alphabetically)

    D3 cell , array formula , Drag down and accross

    HTML Code: 

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

    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
    Quang PT

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

    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 2019
    Posts
    17,428

    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
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1