Run formula if cell in range contains certain text

eforti

Board Regular
Joined
Aug 15, 2005
Messages
220
Hello All,
I'm using the below formula to grab some data from a web query. The issue I'm having is that the web query changes its data output and my cell reference ends up referencing the wrong cells.

My question is this: instead of referencing a specific cell how do I lookup a cell in a range containing certain text, then run the below formula referencing the cell containing the text? The range of cells my web query would run in is C1:C15.

The specific text in the cell can be "Specific Text" for now.

=IFERROR(RIGHT(C8, LEN(C8)-19),"")

Thanks in advance
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
It's long but it works...

=IFERROR(RIGHT(INDEX(C1:C15,AGGREGATE(15,6,ROW(C1:C15)/FIND("Specific Text",C1:C15,1),1)), LEN(INDEX(C1:C15,AGGREGATE(15,6,ROW(C1:C15)/FIND("Specific Text",C1:C15,1),1)))-19),"")
 
Upvote 0
CodeNinja,
Thanks for the quick reply. I tried this code but am getting a blank return. Could you please explain the 15,6 you've included? What is that referencing?
 
Upvote 0
Aggregate function only works for 2010 + ... if you do not have 2010, you will have to write an array formula... 15 refers to the small element of the aggregate function and 6 refers to ignoring errors. Did you try the exact code I have and put a cell including the string "Specific Text" in the range C1:C15?
 
Upvote 0
ADVERTISEMENT
Gotcha,
I'm using 2007 so I guess I need to do an array. Yes I tried the exact code you posted.
 
Last edited:
Upvote 0
Best I can come up with is:

=IFERROR(RIGHT(INDEX(C1:C15,LARGE(ROW(C1:$C$15)*(--NOT(ISERR(FIND("Specific Text",C1:C15,1)))),1)), LEN(INDEX(C1:C15,LARGE(ROW(C1:$C$15)*(--NOT(ISERR(FIND("Specific Text",C1:C15,1)))),1)))-19),"")

use Control Shift Enter to commit this as it is an array formula. If you do not use Ctrl Shift Enter, it will not work...
 
Upvote 0
Thanks CodeNinja,
This works exactly as I had imagined, though it does seem to slow the sheet down significantly. I'm going to happily deal with that for now! Thanks again
 
Upvote 0
Ya, because it is an array formula it will slow the sheet down. If you can avoid using array formulas, you should... Without 2010 aggregate function, I do not know how to avoid that with your current request. Maybe someone will provide you a more efficient formulaic solution than I can.
 
Upvote 0

Forum statistics

Threads
1,196,483
Messages
6,015,460
Members
441,897
Latest member
erma

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