Search for Text within a Range of String, Return IQR of Corresponding Cells

I have been struggling with this formula. I am trying to search for a certain string of text inside a database of larger strings of text, and each time the string is present inside the larger string, I want to put a number corresponding to the text string into the array, and then return the interquartile range of all numbers inside the array.

Simple example:

Assume I am searching for "a:"

sdsasds | 10
fsdss | 24
sawead | 20
ssssss | 28
sddsass | 30
asadaa | 40
jjjjjjj | 23
jaaa | 50

The array would include: 10, 20, 30, 40, and 50, and the inter-quartile range would be 40-20 = 20.

I can piece together parts of the answer:

Please Login or Register  to view this content.

This would return the interquartile range. It searches Row I and then uses Row B for purposes of forming the array. The problem is that the cells in Column I must be exactly "r" as opposed to merely containing "r."

Please Login or Register  to view this content.

This returns instances of "r" inside any string, but the array attempt does not really work.

I was able to use the following as part of another function:

Please Login or Register  to view this content.

Thanks for any and all help!

This array formula, confirmed with CTRL-SHIFT-ENTER...

=PERCENTILE(IF(ISNUMBER(SEARCH("r", I$2:I$15)), B$2:B$15,""), 0.75) - PERCENTILE(IF(ISNUMBER(SEARCH("r", I$2:I$15)), B$2:B$15, ""), 0.25)

Thanks for the feedback.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Read up on the SEARCH() function (F1 in Excel). It finds a string inside another string. So no need to do "*r*".... "r" is enough to search. But you tried it by now, right?

Please reread post #3.

For some reason "r" was returning #NUM (possibly a different error). I have no idea why. "*r*" worked. I just wanted to follow-up with that and see your response before noting that it was solved. Thanks for your help.