Index Formula doesn't work in Excel 2013 but worked in 365

[ceecee88]

Need a little help please,
Why doesn't this work in Excel 2013? I have the same formula in 365 and it work fine, How do I adjust it for Excel 2013, please?
Basically if I typed in apple in Col B Sheet name New List (another sheet), Column D in this sheet should return all value with the word "Apple" (like example in Col E).

Thank you so much for your help.

Result from Excel 2013

Result from 365

 

[ceecee88]

not sure how to upload the sheet, sorry. I tried but can't upload the mini sheet.

just copy/paste your formula here.

oh, here it is. thank you!

=IFERROR(INDEX($B$1:$B$10,AGGREGATE(15,6,(ROW($B$1:$B$10)-ROW($B$1)+1)/ISNUMBER(SEARCH(IF(AND(MID(CELL("filename",'New List'!A1),FIND("]",CELL("filename",'New List'!A1))+1,255),CELL("col")=2),CELL("contents"),""),$B$1:$B$10)),ROWS($D$1:D1))),"")

 

[ceecee88]

I got the mini sheet to work! Here it is.

Book2.xlsx
	A	B	C	D	E	F
1		Apple			Apple
2		Banana			Green Apple
3		Green Apple			Red Apple
4		Red Apple
5		Coconut
6		Orange
7
8
9
10
11
Sheet1

Cell Formulas
Range	Formula
D1	D1	=IFERROR(INDEX($B$1:$B$10,AGGREGATE(15,6,(ROW($B$1:$B$10)-ROW($B$1)+1)/ISNUMBER(SEARCH(IF(AND(MID(CELL("filename",'New List'!A1),FIND("]",CELL("filename",'New List'!A1))+1,255),CELL("col")=2),CELL("contents"),""),$B$1:$B$10)),ROWS($D$1:D1))),"")
D2:D11	D2	=IFERROR(INDEX($B$1:$B$10,AGGREGATE(15,6,(ROW($B$1:$B$10)-ROW($B$1)+1)/ISNUMBER(SEARCH(IF(AND(MID(CELL("filename",'New List'!A2),FIND("]",CELL("filename",'New List'!A2))+1,255),CELL("col")=2),CELL("contents"),""),$B$1:$B$10)),ROWS($D$1:D2))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Formula doesn't work in Excel 2013

Hi, I have the same formula in 365 and it work fine, How do I adjust it for Excel 2013, please? Basically if I typed in apple in Col B Sheet name New List (another sheet), Column D in this sheet should return all value with the word "Apple" (like example in Col E). Here is my current formula...

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.