Replace FILTER function for Excel 2016 user

[jgreer7322]

Hi

I am by no means (at all) an excel expert, but here goes ... I have built a Welsh/english dictionary on O365 Excel, and have used the FILTER function to:

1. search for content in the 'Welsh' column, and display 3 rows (there is an example screenshot below), e.g. look for words that contain 'awr'
2. the same for the 'English' column
3. A search for words that start, or end with, up to three letters, e.g. 'lla'

It all seems to work fine, but ... I want to share the workbook with fellow students, at least of one whom is on Excel 2016, which doesn't support the FILTER function.

So the question is ... how would I rewrite the following for 2016 users?

=IFERROR(FILTER(B2:D4580,ISNUMBER(SEARCH(F1, B2:B4580))), "No Result")
=IFERROR(FILTER('Welsh English'!$B$2:$D$4800,ISNUMBER(SEARCH(C1,(LEFT('Welsh English'!$B$2:$B$4800, 3))))),"No result")

Columns B to D are: Welsh words, English words, Notes respectively, while F1 and C1 in the formulae above are the search boxes.

Thanks in advance for any help you can give!

Cheers.

Jim

 

[Skybluekid]

You need to create a helper column, where you have the SEARCH Function, which will return a 1. You will then have to use the following:
=IF(ROWS($B$2:B2)>COUNTIF([Helper Column],1),"",INDEX($B$2:$B$4580,AGGREGATE15,6,ROW($B$2:$B$4580)-ROW($B$1)/[Helper Column=1),ROWS($B$2:B2))
=IF(ROWS($B$2:B2)>COUNTIF([Helper Column],1),"",INDEX($C$2:$C$4580,AGGREGATE15,6,ROW($B$2:$$4580)-ROW($B$1)/[Helper Column=1),ROWS($B$2:B2))
=IF(ROWS($B$2:B2)>COUNTIF([Helper Column],1),"",INDEX($D$2:$D$4580,AGGREGATE15,6,ROW($B$2:$B$4580)-ROW($B$1)/[Helper Column=1),ROWS($B$2:B2))

 

[jgreer7322]

Thank you!

 

[Skybluekid]

No Problem, if this works for you, please could you mark it as a solution

 

[jgreer7322]

I got this earlier, which seems to have worked, but hank you again:

 

[Peter_SSs]

=IFERROR(FILTER(B2:D4580,ISNUMBER(SEARCH(F1, B2:B4580))), "No Result")

Just a note: There is no need for the IFERROR since the FILTER function already has a built-in optional argument to deal with the case where the filter returns no results. See cell I2 below.

I got this earlier, which seems to have worked

A slight word of warning: If any rows are subsequently inserted at the top of the worksheet, that C+S+E formula will return incorrect results.

Formulas that require C+S+E confirmation often cause problems when users edit formulas and forget to re-confirm that way or to select the relevant range first.
In columns N:O below I have shown an alternative that does not require the C+S+E confirmation. It requires normal entry then copy across your 3 columns and down as far as you might ever need. (Also, no issues with this one if more rows are inserted at the top).

Your original formula included to produce the results in columns E:G for comparison.

23 04 24.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1					c				gg				c
2		az	C2	D2
3		kb	C3	D3	cy	C4	D4		No Result				cy	C4	D4
4		cy	C4	D4	qc	C6	D6						qc	C6	D6
5		xd	C5	D5	cc	C8	D8						cc	C8	D8
6		qc	C6	D6
7		aa	C7	D7
8		cc	C8	D8
9		bb	C9	D9
Replace FILTER

Cell Formulas
Range	Formula
E3:G5	E3	=IFERROR(FILTER(B2:D4580,ISNUMBER(SEARCH(E1, B2:B4580))), "No Result")
I3	I3	=FILTER(B2:D4580,ISNUMBER(SEARCH(I1, B2:B4580)), "No Result")
M3:O9	M3	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$4580)/ISNUMBER(SEARCH($M$1,$B$2:$B$4580)),ROWS(M$3:M3))),"")
Dynamic array formulas.

 

[jgreer7322]

Thank you!

Jim

 

[jgreer7322]

Peter: As I said at the top: 'I am by no means (at all) an excel expert, but here goes ...' (ain't that the truth!). The original formulas were written with a great deal of googling involved; what would be a good book(s)/training to develop my current (almost nonexistent) Excel skills?

Cheers.

Jim

 

[Peter_SSs]

Sorry, but I am not a good one for books/training suggestions. I gained most of my knowledge from work colleagues, built-in Help, lots of trial & error and lots of time spent in this forum.
However, one place to look would be under the MrExcel Publishing drop-down at the top of this forum.

 

[jgreer7322]

Cool. Thank you!