Convert Formula from .xlsx Version to .xls

[pineapple61]

Hi everyone, someone helped me create a formula to filter an excel sheet (the thread post can be found here: Filtering One Condition to Multiple Columns on a Drop Down List). However, since I created this formula on the Office 365, 2021 version (.xlsx), when I open the file in the Office 2016 version (.xls) the formulas are no longer valid. I tried converting the file from .xlsx to .xls but it did not work. Could anyone help me with this?

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	First Job	Second Job	Third Job			Doctor
2	John	Doctor	Police	Mechanic		Name	First Job	Second Job	Third Job
3	Marcus	Fireman	Salesman	Waiter		John	Doctor	Police	Mechanic
4	Kyle	Doctor	Engineer	Fireman		Kyle	Doctor	Engineer	Fireman
5	Alexa	Engineer	Doctor	Doctor		Britney	Doctor	Mechanic	Waitress
6	Britney	Doctor	Mechanic	Waitress		Alexa	Engineer	Doctor	Doctor
7	Ferguson	Police	Fireman	Engineer
Test

Cell Formulas
Range	Formula
F3:I6	F3	=UNIQUE(VSTACK(FILTER($A$2:$D$7,$B$2:$B$7=G1,""),FILTER($A$2:$D$7,$C$2:$C$7=G1,""),FILTER($A$2:$D$7,$D$2:$D$7=G1,"")))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
F1:G1	List	Doctor, Fireman, Engineer, Police, Salesman, Mechanic, Waiter, Waitress

 

[awoohaw]

It is not xls vs xlsx. The functions VSTACK, FILTER, and UNIQUE are not available in Excel 2016. You'll have to build the equation differently.

 

[shinigamilight]

Try this ctrl shift enter if you're not on 365

=IFERROR(INDEX($A$2:$D$7,AGGREGATE(15,6,ROW($A$2:$A$7)/--(FREQUENCY(IF($B$2:$D$7=$G$1,ROW($A$2:$A$7)),ROW($A$2:$A$7))>0)-1,ROW($A1)),COLUMN(A$1)),"")

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	First Job	Second Job	Third Job			Doctor
2	John	Doctor	Police	Mechanic		Name	First Job	Second Job	Third Job
3	Marcus	Fireman	Salesman	Waiter		John	Doctor	Police	Mechanic
4	Kyle	Doctor	Engineer	Fireman		Kyle	Doctor	Engineer	Fireman
5	Alexa	Engineer	Doctor	Doctor		Alexa	Engineer	Doctor	Doctor
6	Britney	Doctor	Mechanic	Waitress		Britney	Doctor	Mechanic	Waitress
7	Ferguson	Police	Fireman	Engineer
8
Main

Cell Formulas
Range	Formula
F3:I7	F3	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$7)/($B$2:$D$7=$G$1)/(ISNA(MATCH($A$2:$A$7,$F$2:$F2,0))),1)),"")

 

[pineapple61]

Try this ctrl shift enter if you're not on 365

=IFERROR(INDEX($A$2:$D$7,AGGREGATE(15,6,ROW($A$2:$A$7)/--(FREQUENCY(IF($B$2:$D$7=$G$1,ROW($A$2:$A$7)),ROW($A$2:$A$7))>0)-1,ROW($A1)),COLUMN(A$1)),"")

It worked! This made me so happy, you are a lifesaver!!!!!!!!!

 

[pineapple61]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	First Job	Second Job	Third Job			Doctor
2	John	Doctor	Police	Mechanic		Name	First Job	Second Job	Third Job
3	Marcus	Fireman	Salesman	Waiter		John	Doctor	Police	Mechanic
4	Kyle	Doctor	Engineer	Fireman		Kyle	Doctor	Engineer	Fireman
5	Alexa	Engineer	Doctor	Doctor		Alexa	Engineer	Doctor	Doctor
6	Britney	Doctor	Mechanic	Waitress		Britney	Doctor	Mechanic	Waitress
7	Ferguson	Police	Fireman	Engineer
8
Main

Cell Formulas
Range	Formula
F3:I7	F3	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$7)/($B$2:$D$7=$G$1)/(ISNA(MATCH($A$2:$A$7,$F$2:$F2,0))),1)),"")

This formula also worked! Thank you so so much, you guys are the best!

 

[Fluff]

Glad we could help & thanks for the feedback.