Search in range instead of single cell

[ceecee88]

Hi, the current formula is asking Excel to search $B$2 (single cell), is there a way to make it search in a range (search in any active cell in the range)? For example if I type anywhere in $B$2:$B$10, I get the result in $C2 down
I tried =IFERROR(INDEX($A$2:$A$15,AGGREGATE(15,6,(ROW($A$2:$A$15)-ROW($A$2)+1)/ISNUMBER(SEARCH($B$2:$B$10,$A$2:$A$15)),ROWS(C$2:C2))),"") It doesn't work ^^"

Thank you!

 

[Peter_SSs]

I have noticed in other posts you require answers that work in both your listed versions rather than just 365. If that is the case then you should specify that in post 1 of your threads.
Is that the case for this thread?

For example if I type anywhere in $B$2:$B$10, I get the result in $C2 down

Could there be more than one cell with data in B2:B10 or just a single cell but placed anywhere?

Could we have sample data and expected results that shows the circumstance(s) that you are referring to?

 

[ceecee88]

I have noticed in other posts you require answers that work in both your listed versions rather than just 365. If that is the case then you should specify that in post 1 of your threads.
Is that the case for this thread?


Could there be more than one cell with data in B2:B10 or just a single cell but placed anywhere?

Could we have sample data and expected results that shows the circumstance(s) that you are referring to?

This is about the question but on a different part, on the first one my question was how to turn filter into older version I wasn't thinking about the search function as I thought I already had it -> ISNUMBER(SEARCH(INDIRECT(CELL("address")),$A$2:$A$15)) to search any on active cell on the sheet.

I got the filter part fixed, thanks to this board but now I'm thinking to search anywhere will slow the sheet down unnecessary when I only need it to search in particular area not the whole sheet. So it was kinda different part of the question in the same question. Not sure if that explain it?


Yes, there could be more than 1 cells with data in B2:B10 (so the formula would eventually search text from any active cell in the range
For example, if I type Juice in B3 and hit enter the list from D2 showing items with juice only, if I type Apple in B2 the list from D2 will change to show items with Apple only

Currently I have it formula that would work on any active cells on the sheet (I can type anywhere), but I want to limit to only range B2:B10 so that it wouldn't slow down the sheet.

So far I tried this but it not working properly
=IFERROR(INDEX($A$2:$A$15,AGGREGATE(15,6,(ROW($A$2:$A$15) ROW($A$2)+1)/ISNUMBER(SEARCH(INDIRECT(IF(CELL("col")=2,CELL("contents"),"")),$A$2:$A$15)),ROWS(E$2:E2))),"")

Hope this clarify it better.
Thank you!

 

[ceecee88]

This is about the question but on a different part, on the first one my question was how to turn filter into older version I wasn't thinking about the search function as I thought I already had it -> ISNUMBER(SEARCH(INDIRECT(CELL("address")),$A$2:$A$15)) to search any on active cell on the sheet.

I got the filter part fixed, thanks to this board but now I'm thinking to search anywhere will slow the sheet down unnecessary when I only need it to search in particular area not the whole sheet. So it was kinda different part of the question in the same question. Not sure if that explain it?


Yes, there could be more than 1 cells with data in B2:B10 (so the formula would eventually search text from any active cell in the range
For example, if I type Juice in B3 and hit enter the list from D2 showing items with juice only, if I type Apple in B2 the list from D2 will change to show items with Apple only

View attachment 68770

Currently I have it formula that would work on any active cells on the sheet (I can type anywhere), but I want to limit to only range B2:B10 so that it wouldn't slow down the sheet.

So far I tried this but it not working properly
=IFERROR(INDEX($A$2:$A$15,AGGREGATE(15,6,(ROW($A$2:$A$15) ROW($A$2)+1)/ISNUMBER(SEARCH(INDIRECT(IF(CELL("col")=2,CELL("contents"),"")),$A$2:$A$15)),ROWS(E$2:E2))),"")

Hope this clarify it better.
Thank you!

View attachment 68771

 

[ceecee88]

I think I might have it just getting rid of indirect in that last formula and it work on the same sheet!^^
=IFERROR(INDEX($A$2:$A$15,AGGREGATE(15,6,(ROW($A$2:$A$15) ROW($A$2)+1)/ISNUMBER(SEARCH(IF(CELL("col")=2,CELL("contents"),""),$A$2:$A$15)),ROWS(E$2:E2))),"")

Now I'm working on make it work on a different sheet. Any quick suggestion?

 

[ceecee88]

I think I might have it just getting rid of indirect in that last formula and it work on the same sheet!^^
=IFERROR(INDEX($A$2:$A$15,AGGREGATE(15,6,(ROW($A$2:$A$15) ROW($A$2)+1)/ISNUMBER(SEARCH(IF(CELL("col")=2,CELL("contents"),""),$A$2:$A$15)),ROWS(E$2:E2))),"")

Now I'm working on make it work on a different sheet. Any quick suggestion?

I got it! using MID function to find CELL("filename") before CELL("col").

Thanks