Combining two formulae

m_in_spain

Board Regular
Joined
Sep 28, 2018
Messages
64
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.
Excel Formula:
=LET(r,MATCH("zzz",$E:$E),rng,$E$2:INDEX($E:$E,r),FILTER(rng,ISNUMBER(SEARCH(G2,rng)*SEARCH(G3,rng)),""))
 
Upvote 0
Solution
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
 
Upvote 0
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
 
Upvote 0
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.
Excel Formula:
=$E$2:INDEX($E:$E,MATCH("zzz",$E:$E))
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top