# INDEX MATCH IF (then list alphabetically)

1. ## 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  Register To Reply

2. ## Re: INDEX MATCH IF (then list alphabetically)

post a sample sheet ( see yellow banner)  Register To Reply

3. ## Re: INDEX MATCH IF (then list alphabetically)

See attached example sheet, with values manually entered in column D to show expected results  Register To Reply

4. ## Re: INDEX MATCH IF (then list alphabetically)

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

Let us know if you have any questions.  Register To Reply

5. ## 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  Register To Reply

6. ## Re: INDEX MATCH IF (then list alphabetically)

In E3 and down:

=SUMIF(A3:A9,D3,B3:B9)  Register To Reply

7. ## 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)))&""``  Register To Reply

8. ## 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  Register To Reply

9. ## 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  Register To Reply

10. ## 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.  Register To Reply

11. ## Re: INDEX MATCH IF (then list alphabetically)

And thank you for ignoring me   (just kidding.....)  Register To Reply