Excel Search Index and Trim Matched Row

Question

I am trying to search for a row and return only part of the text in the row

I'm using this formula

=IFERROR(IF(SEARCH("LineItem #",B16),INDEX($B19:$B34,MATCH("*"&"Thermal Requirement"&"*",$B19:$B34,0))),"")

which returns the full row. Is there a way to pick out part of the row. for example just pull U-Factor = 0.35 from the row.

I know I can do this with a helper column but I am wanting to do it all in the same column if possible.

Answer 1

If you have a version of Excel that supports Dynamic Arrays, you could use a combination of Filter and FilterXML instead of your Index formula, to filter out only the U-Factor bits

Formula in E17 (Spills to E17:E18 for this sample data)

=FILTERXML("<h><a>"&SUBSTITUTE(FILTER(B19:B30,LEFT(B19:B30,19)="Thermal Requirement"),",","</a><a>")&"</a></h>","//a[contains(text(),""U-Factor"")]")```

or put the search terms in seperate cells

=FILTERXML("<h><a>"&SUBSTITUTE(FILTER(B19:B30,LEFT(B19:B30,LEN(E9))=E9),",","</a><a>")&"</a></h>","//a[contains(text(),""" & E10 & """)]")

Note that if there are more than 1 "Thermal Requirement" line in the data range, this will Spill

---

How it work

Broken done and refering to the below image:

The inner Filter returns a Spill range of just "Thermal Requirement" lines

=FILTER(B19:B30,LEFT(B19:B30,LEN(E9))=E9)

This Spill range is then formatted as XML

="<h><a>"&SUBSTITUTE(G17#,",","</a><a>")&"</a></h>"

The XML is then filtered to return the "U-Factor" nodes

=FILTERXML(H17#,"//a[contains(text(),""" & E10 & """)]")