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
=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))),"")

Thank you so much for your help.

Result from Excel 2013
Capture.PNG


Result from 365
Capture.PNG

The formula didn't work on Excel 2016 also.
I think, it's might be different behavior of array formula of 365 and others.

Regards.

Any suggestion on fixing it please?

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Hi, thank you and sorry about that. I updated my original post and attached the workbook already.

In E2 copied down (I think):

=IFERROR(INDEX($B$1:$B$500,AGGREGATE(15,6,ROW($1:$500)/(RIGHT($B$1:$B$500,LEN($E$1))=$E$1),ROW(1:1))),"")

I'm not sure what do you really want from the formula.

Please try

D1
=IFError(vlookup(E1,$B$1:$B$6,1,false),"")

Regards.

Hi again, thank you but I don't see any part in the formula that lookup the value in Col B on another sheet as mentioned. I ran the formula test on both versions and think the issue is in that search part. But thank you for your input though. Not sure if I explain what I want clear enough, anything else you suggest me to explain more in the post?

You need to explain again and more clearly what you are actually trying to do.

Is this it???

=IFERROR(INDEX($B$1:$B$500,AGGREGATE(15,6,ROW($1:$500)/(RIGHT($B$1:$B$500,LEN('New List'!$B$2))='New List'!$B$2),ROW(1:1))),"")

Really NOT clear to me at all - sorry.

Then try

E1
=IFERROR(INDEX($B$1:$B$6,AGGREGATE(15,6,ROW($B$1:$B$6)/(1/(SEARCH('New List'!$B$2,Sheet1!$B$1:$B$6)>0)),ROW())),"")

Regards.

FYI, cross-posted: https://www.mrexcel.com/board/thread...in-365.1215355

This MIGHT be what you want and WILL work in 2013!!

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(Sheet1!$B$1:$B$20)/(ISNUMBER(SEARCH(LOOKUP(2,1/('New List'!B:B<>""),'New List'!B:B),Sheet1!$B$1:$B$20))),ROWS(E$2:E2))),"")

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new here, I shall do it for you this time: https://www.mrexcel.com/board/thread...n-365.1215355/)

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

I am so sorry I tested with a wrong version, it worked in 365 but doesn't work in 2013. When I type new word in New List COL B in 2013 the list doesn't change like it does in 365. Any other suggest please?

Update: I tested carefully again and found out it is not that it doesn't work but it only work if I type downward. For example, if I type in B2 then B3 then B4, it work fine. Then I went back and edit B3 that when it stop working so the first time testing I thought the whole thing doesn't work on 2013. Is there a way to make it work for editing the above cell as well? Thank you so much.

Are you entering it with CTRL+SHIFT+ENTER? If not, then you may need to.

Hi, yes, I did. the list doesn't change though. I typed in Apple, it still show Banana from when I tested with 365.
Attachment 794552

I am confused - in your last but one post you said it was working in 365, now you say it isn't - can you please clarify which versions work in 2013, which is what you wanted?

Pleae read my PM to you just now. Thanks.

Hi, for sorry for the confusion but I never said it doesn't work in 365. The formula I had to begin with and what Glenn came up with produced exactly what I need and they are both work only in 365 and not 2013. Hope this clarify it. Sorry again for the confusion.

You said this:

I typed in Apple, it still show Banana from when I tested with 365.

Thais indicates that it doesn't work in 365.

OK - yes, it did confuse me. Thanks for clarifying.

Not sure why they are not working - they don't use any post-2013 functions.

If you post it on any more forums, remember you MUST post links here. Thanks.

it will (should) work in 2013 & 365.

But.

You randomly type keywords ANYWHERE in column B?? I thought you meant there would only ever be ONE value in column B... or (at worst) you would be working downwards.

If there can be multiple values in column B and you want Excel to "choose" one of them, then you MUST have a rule that defines which one is chosen.


What is the rule?

Originally Posted by Glenn Kennedy

it will (should) work in 2013 & 365.

But.

You randomly type keywords ANYWHERE in column B?? I thought you meant there would only ever be ONE value in column B... or (at worst) you would be working downwards.

If there can be multiple values in column B and you want Excel to "choose" one of them, then you MUST have a rule that defines which one is chosen.


What is the rule?

ํYes, I'm looking for the formula that will garb value from any active cell in the range not just one or only downward. My original formula work just like that but unfortunately it doesn't work on 2013, and I wonder if this is possible in 2013 or may be not...

This one work perfectly on 365 --> the rule is > any active cell on specific range, you can type up or down in any order it will grab the active cell value in the range.

=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))),"")

I do not understand what you mean when you say "that will grab value in any active cell in specific range".

Explain again, slowly & clearly.

Originally Posted by Glenn Kennedy

I do not understand what you mean when you say "that will grab value in any active cell in specific range".

Explain again, slowly & clearly.

So the formula that you came up with is only grab value from the last row and return it right?

If there is value in A1 A2 A3 A4, it will return value from A4. If I go up and type in the cell above let say A3 while A4 has something in it, the formula won't work anymore. It will not grab value from the last cell I type (A3) but grab value of last row (A4).

"that will grab value in any active cell in specific range". means, I look for the formula that will grab value from any active cell doesn't matter what order it is in that range just any cell that is active (active = double click and type somthing in it and enter). Normally we can use =indirect(cell("address")) to refer to active cell but this formula will look overall the place, anywhere in the worksheet which take time to calculate. So I only want it to look for active cell in specific range.

Don't know if that help clarify it a bit more?

Show me how INDIRECT(CELL("address")) is working in a sample sheet.

A1 has the formula =INDIRECT(CELL("address")), type in any cell in the worksheet and hit enter -> A1 will return that value. I uploaded testing file called "Indirect Cell address testing.xlsx‎" in my original post.

And if you test the other file on my original post on Excel 365, anything you type in sheet "New List" ColB and hit enter, check another sheet, it will return all values that has the word you just type in sheet "New List" ColB. If you type outside of that range (sheet "New List" ColB), then nothing happen.

Thank you

Attachment 794665

All that monkeying around with INDIRECT & CELL feels very flaky. Change the active cell ands it all falls over.

Play with this and see if it suits. I can then explain it (as required).

Type your search term anywhere in the coloured cells in New List column B. The bright blue cells in sheet 1 populate correctly. Add another search term in NL column B. The blue list updates to give you the result corresponding to the most recent entry in the purple cells.

Originally Posted by Glenn Kennedy

All that monkeying around with INDIRECT & CELL feels very flaky. Change the active cell ands it all falls over.

Play with this and see if it suits. I can then explain it (as required).

Type your search term anywhere in the coloured cells in New List column B. The bright blue cells in sheet 1 populate correctly. Add another search term in NL column B. The blue list updates to give you the result corresponding to the most recent entry in the purple cells.

Hi Glenn, thank you so much! I tested it and it work! I would say 95% of the time, so I had a look at what happened with the other 5% and found you have set up the time at AZ to trigger the formula. So the 5% that didn't work was the one I already typed in and went back to edit (without deleting it first and the original time stamp wasn't the latest).



The formula look clean and will work most of the time and a good fit if use in content that doesn't have too much rows, but if you have 50,000 of data mean you need another 50,000 row of helper column. I really like the idea though; simple, clean and yet clever.

If I may, I wanted to ask about the CELL("address") formula (I know you didn't like it lol I agree with you it can be a bit flaky but for me I think it does the job well and doesn't effect user experience from what I have been testing so far) but you do think it is possible to define a range in the formula (for 2013) so it doesn't look all over the worksheet?

Thank you so much for your help, I really appreciate you time.

I'm not aware of any way to restrict cell in the way you want.

Excel has 16,000+ columns and 1,000,000+ rows, so don't worry about a helper column. If its visibility offends you... put it in AZ, or in a hidden sheet...

In your REAL sheet you will need to enable Iterative calculations: File/Options/Formulae and check the box.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.