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

 

[RobP]

Hi,

I'm not sure if this is the reason, but I've read that INDEX was updated in O365 to work a little differently. In order for it to function as it used to, you put an "@" sign in front if it.

so INDEX would become @Index (ah, sorry this is vice versa I just realised.. the @Index would make index perform as it used to from 2013, when you are using 365.

I am guessing your 365 implementation is therefore not understood by 2013.. which you know I know - but I suspect its in this INDEX issue


Rob

 

[ceecee88]

Hi,

I'm not sure if this is the reason, but I've read that INDEX was updated in O365 to work a little differently. In order for it to function as it used to, you put an "@" sign in front if it.

so INDEX would become @Index

Maybe worth a shot ?
Rob

Hi, thank you, I tried it didn't work. I'm thinking the search part has a problem but still can't figure out the solution (it is easier if the value is in the same sheet but in this case it is on the difference sheet). Still trying ^^

 

[RobP]

yeah, sorry, I updated my original comment above after realising I was back to front..

 

[RobP]

INDEX is an Array formula in O365, but it wasnt in 2013 I believe ..

 

[ceecee88]

yeah, sorry, I updated my original comment above after realising I was back to front..

I ran the evaluate formula and found the problem in the search part that give difference result on 2013 than 365, but can't figure out how to adjust it yet. T_T

 

[RobP]

might be useful if you were to actually post the formula here using the XLS wrapper above, so people could look at it for you, rather than at a picture ?

 

[6StringJazzer]

INDEX is an Array formula in O365, but it wasnt in 2013 I believe ..

365 seems to implicitly interpret anything as an array formula that can be interpreted as an array formula. INDEX is not always an array formula but can be in 365 depending on what arguments you give it. However, this overall formula is explicitly entered as an array formula in both cases so that issue should not make a difference.

 

[ceecee88]

might be useful if you were to actually post the formula here using the XLS wrapper above, so people could look at it for you, rather than at a picture ?

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

 

[RobP]

just copy/paste your formula here.