Combining two formulae

[m_in_spain]

Hi
I have been battling this one for hours!
I have two formulae one that looks down Column E for specific text that is held in cell G2, the second looks down the same ColumnE for specific text held in G3.
Both work well and return the value of the specific cell to cell E1.
I have been trying to combine this for one formula tooks down Column E for both Texts (G2 & G3) and retruns the cell found to cell E1
Cell G2 is a constant text, while G3 can alter, however in the searched list in Column E there are other items with similar name to that in G2, and indeed with the same text as in G3, so i do need to check for G2
Formulae:
=IFERROR(INDEX($E$2:$E$200, SMALL(IF(ISERROR(SEARCH(G2, $E$2:$E$200)), "", MATCH(ROW($E$2:$E$200), ROW($E$2:$E$200))), ROW(B1))), "")
and
=IFERROR(INDEX($E$2:$E$200, SMALL(IF(ISERROR(SEARCH(G3, $E$2:$E$200)), "", MATCH(ROW($E$2:$E$200), ROW($E$2:$E$200))), ROW(B1))), "")

I want one that does both! i.e. the cell value that goes back to E1 must have both text strings from G2 & G3

While furthering my knowledge of excel, i would rather the search column is E2 to last used row in Column E, I suspect that bit is easier!
And finally the lat bit of these formaule, (which i worked out through scavanging google), ROW(B1) - what is the function of that statement?, originally somehow it had got itself to ROW(AF1) which is way outside my data area, so i changed it to B1 (which also happens to be the source for cell G3, but this change appeared to make not a jot of difference.
Many thanks for reading this and any help will be most appreciated.

 

[jasonb75]

Taking a different approach to the task as the formula method you are currently using is a little outdated (excel 2007 method).
This may or may not work for you depending in your version of excel. It would help if you updated your account details to show which version you are using. Remember to scroll down and 'Save Changes' after making your choice.

=LET(r,MATCH("zzz",$E:$E),rng,$E$2:INDEX($E:$E,r),FILTER(rng,ISNUMBER(SEARCH(G2,rng)*SEARCH(G3,rng)),""))

 

[m_in_spain]

Hi,
Thanks.
I have updated the profile page (office 365) and saved the page
When i try your suggestion excel objects to the r in =LET(r

 

[m_in_spain]

Hi,
Thanks.
I have updated the profile page (office 365) and saved the page
When i try your suggestion excel objects to the r in =LET(r

However, now i find the part after ,... Filter etc works like a dream thanks!
I guess the first part is sorting out my unknown quantity of lines in column E?
Thanks tho, =FILTER(E2:E200,ISNUMBER(SEARCH(G2,E2:E200)*SEARCH(G3,E2:E200)),"") works like i had hoped

 

[jasonb75]

Not sure why it would object to that unless you don't have the LET update yet.

That part was to make it dynamic, without LET, you would have a much longer formula as you would need to make each individual section dynamic rather than setting it once at the start.

You could set it as a dynamic named range using a similar method as a definition.

=$E$2:INDEX($E:$E,MATCH("zzz",$E:$E))

 

[m_in_spain]

Many thanks for your help.
Your solution got my sheet working entirely thanks to the Filter and Isnumber.
I will now scratch arount the internet to learn more about filter and isnumber. Getting it working is one thing, but now i must understand why!

 

[jasonb75]

I will now scratch arount the internet to learn more about filter and isnumber.

Click into the cell with the formula and position your cursor somewhere in the name of one of the functions, then press f1. This will open the help file for that function and tell you what it does.

 

[m_in_spain]

Thanks, that was more good info!
I also found out that my office 365 has not received the LET function as of yet, i read something about alternate six month cycles of update, so despite there being a full update last week, it seems i am not on the correct cycle! I could ask my IT department, (as i have zero admin rights) but then if i use it, none of my colleagues will have it so the formulae wont work! I will download the correct update on my personal laptop to give LET a try.
Thanks again