when filter function will be available?

[lezawang]

Hi
I just watched a video and ExcelIsFun author said Filter() not not available now. He manged to do a lot of stuff without using array function anymore. That is a big change.

My second question, is Microsoft going to get rid of array formula?

https://www.youtube.com/watch?v=cB6UlIwpwVc

 

[lezawang]

Another question, I did use exactly the same formula he used but did not work. When I typed the formula in the cell B2 and press enter. It only applied to the first cell (it did not copy over like what happen to him). Then I had to use autoFill to copy the formula over, but also did not work. Then I had to use absolute address like below

=MATCH($B$2:$B$5,$A$2:$A$12,0)

My question, how come for him he did not have to use absolute address? I am also using Office 365

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]list1[/TD]
[TD="class: xl65, width: 64"]list2[/TD]
[TD="class: xl66, width: 64"]new list[/TD]
[/TR]
[TR]
[TD="class: xl66"]mike1[/TD]
[TD="class: xl66"]linda1[/TD]
[TD="class: xl66, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl66"]alex1[/TD]
[TD="class: xl66"]alex2[/TD]
[TD="class: xl66, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl66"]mary1[/TD]
[TD="class: xl66"]mary[/TD]
[TD="class: xl66, align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl66"]sarah1[/TD]
[TD="class: xl66"]alex1[/TD]
[TD="class: xl66, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66"]john1[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]linda1[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]mike2[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]alex2[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]mary2[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]sarah2[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]john2[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Works fine for me......Place the formula in C2 and drag down

 

[Peter_SSs]

Another question, I did use exactly the same formula he used but did not work. When I typed the formula in the cell B2 and press enter. It only applied to the first cell (it did not copy over like what happen to him). Then I had to use autoFill to copy the formula over, but also did not work. Then I had to use absolute address like below

=MATCH($B$2:$B$5,$A$2:$A$12,0)

My question, how come for him he did not have to use absolute address? I am also using Office 365

See here for more info but an extract:

Note: Dynamic array functions are currently a beta feature, and only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.

 

[lezawang]

Thank you all. Now I did it in my way, using absolute reference and then autofill. I also created a new column using ISNA() function and I got False and True. Now I want to change the last column to name instead of False/True like what Gravin did in his video. Thank you very much

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl66, width: 64"]list1[/TD]
[TD="class: xl66, width: 64"]list2[/TD]
[TD="class: xl66, width: 64"]=MATCH($B$2:$B$5,$A$2:$A$12,0)[/TD]
[TD="class: xl66, width: 64"]isna()[/TD]
[/TR]
[TR]
[TD="class: xl65"]mike1[/TD]
[TD="class: xl65"]linda1[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl65"]alex1[/TD]
[TD="class: xl65"]alex2[/TD]
[TD="class: xl65, align: right"]8[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl65"]mary1[/TD]
[TD="class: xl65"]mary[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: xl65"]sarah1[/TD]
[TD="class: xl65"]alex1[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl65"]john1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]linda1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]mike2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]alex2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]mary2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]sarah2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]john2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Is this what you are trying to achieve?

Excel Workbook
	A	B	C
1	list1	list2
2	mike1	linda1	alex1
3	alex1	alex2	linda1
4	mary1	mary	alex2
5	sarah1	alex1
6	john1
7	linda1
8	mike2
9	alex2
10	mary2
11	sarah2
12	john2
List