Filter for older versions

[ceecee88]

Hi, how do I translate this formula for an older version?

I'm thinking offset but can't find the way to make it work.

Thank you!

 

[Fluff]

That formula makes no sense & will create a circular reference.
Are you trying to return all values that contain the word in C1?

 

[ceecee88]

That formula makes no sense & will create a circular reference.
Are you trying to return all values that contain the word in C1?

That's is correct, I want to do dropdown list with it but multiple dropdown on every row that's why I'm using CELL address (when I type on each cell it will show the list that contain typed text) and yes it would show circular reference but it serve the purpose. However, I would love to hear some other ideas.

Thank you!

 

[Fluff]

Sorry but I don't understand, you cannot type into C2 if there is a formula there.

 

[ceecee88]

Sorry but I don't understand, you cannot type into C2 if there is a formula there.

I type into C1 or any cell rather than C2 and whatever I type in the cell it will show the list that contain typed text from C2 down. For example, I type in D2 Orange and click dropdown list the list only show item contain Orange and I can type anywhere as long as I created the dropdown list there which using OFFSET from C2 down. But all of this I need to do it in older version which I can't use filter.

Not sure if that help clarify it a better?

 

[Fluff]

But that formula is not looking at C1.

 

[Fluff]

If you put this formula in C2 & drag down it will return the values in col A that match C1

=IFERROR(INDEX($A$2:$A$15,AGGREGATE(15,6,(ROW($A$2:$A$15)-ROW($A$2)+1)/(ISNUMBER(SEARCH($C$1,$A$2:$A$15))),ROWS(C$2:C2))),"")

 

[ceecee88]

But that formula is not looking at C1.

yes, it can look anywhere on the sheet (active cell)

 

[ceecee88]

If you put this formula in C2 & drag down it will return the values in col A that match C1

=IFERROR(INDEX($A$2:$A$15,AGGREGATE(15,6,(ROW($A$2:$A$15)-ROW($A$2)+1)/(ISNUMBER(SEARCH($C$1,$A$2:$A$15))),ROWS(C$2:C2))),"")

If you put this formula in C2 & drag down it will return the values in col A that match C1

=IFERROR(INDEX($A$2:$A$15,AGGREGATE(15,6,(ROW($A$2:$A$15)-ROW($A$2)+1)/(ISNUMBER(SEARCH($C$1,$A$2:$A$15))),ROWS(C$2:C2))),"")

This is work perfectly with a bit of address adjustment. Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.