Return contents of a cell that contains a specific text within a range.

**nhi** · 02-01-2013, 11:46 AM

Return contents of a cell that contains a specific text within a range.

For Example,
------ A------ B------ C------
1 -----22HP-- DD --- T3------

Within the range from A1-C1, the formula finds the text "HP" in A1 and returns 22HP.

Thank you,

**twiggywales** · 02-01-2013, 11:51 AM

maybe

Please Login or Register  to view this content.

entered with Control+Shift+Enter

**nhi** · 02-01-2013, 12:02 PM

That methods works, however, when i try to continue the function with additional rows and data I recieve all blanks (If HP is in column C, it will not return the value). Also, is there a way I can just automatically extend the function without using ctrl-shift-enter everytime?

**twiggywales** · 02-01-2013, 12:12 PM

if you upload a small sample it would be much more straight forward to work on

**nhi** · 02-01-2013, 12:20 PM

Attached. Thanks

**twiggywales** · 02-01-2013, 12:48 PM

ok here you go switched to a different kinda search

Sample File.xlsx

**nhi** · 02-04-2013, 07:54 PM

Originally Posted by twiggywales

ok here you go switched to a different kinda search

Attachment 211401

Hello again,

Can you explain the logic behind this equation? Just trying to wrap my head around this.

=IFERROR(INDEX(A4:C4,,MATCH(TRUE,INDEX(ISNUMBER(SEARCH("HP",A4:C4)),,),0)),"")

1. Search for HP in this the array of A4:C4
2. If one of those values is a number then state True (part that confuses me because when i break out the formula it is always false)
3. Match anything that is true
4. Index: cell location

Thank you very much.