Search string in range of cells and display full cell contents

[BoyScout]

Hi,

I need to search a range of cells for a given string of text and display the cell contents for any cells in the row which contain the search criteria.

Example:-
String to search is entered in B15. I need it to search each row and display (in column G) the full data for the match. No more than one cell in each row will contain matching data.

	A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>
1</SPAN>	Machine</SPAN>	User 1</SPAN>	User 2</SPAN>	User 3</SPAN>	User 4</SPAN>	User 5</SPAN>	Full text of cell containing the Search String</SPAN>	Desired result in column G</SPAN>
2</SPAN>	Alpha</SPAN>	ghl.792.c7x</SPAN>	abc.587.k2g</SPAN>	alt.982.g5z</SPAN>	qly.496.b6s</SPAN>	qpn.735.y1h</SPAN>	What is the Formula?</SPAN>	abc.587.k2g</SPAN>
3</SPAN>	Bravo</SPAN>	own.731.h6x</SPAN>	rmx.183.w4h</SPAN>	wpz.388.g2s</SPAN>	abc.584.w6c</SPAN>	abc.587.w7f</SPAN>	What is the Formula?</SPAN>	abc.587.w7f</SPAN>
4</SPAN>	Charlie</SPAN>	abc.589.w7l</SPAN>	alm.921.w8j</SPAN>	abc.587.h8e</SPAN>	vxn.741.w6k</SPAN>	qnp.552.l1c</SPAN>	What is the Formula?</SPAN>	abc.587.h8e</SPAN>
5</SPAN>	Delta</SPAN>	arc.365.y4d</SPAN>	wpv.714.r2d</SPAN>	qpk.795.e1a</SPAN>	adc.587.w8h</SPAN>	whr.198.v5o</SPAN>	What is the Formula?</SPAN>
6</SPAN>	Echo</SPAN>	abc.587.l1j</SPAN>	pwm.735.t1s</SPAN>	jhm.726.w3w</SPAN>	mkn.723.b6b</SPAN>	ops.717.s2t</SPAN>	What is the Formula?</SPAN>	abc.587.l1j</SPAN>
7</SPAN>	Foxtrot</SPAN>	asg.356.w6d</SPAN>	lfr.483.j4v</SPAN>	pfd.587.h5f</SPAN>	abc.587.p7t</SPAN>	alt.460.w8j</SPAN>	What is the Formula?</SPAN>	abc.587.p7t</SPAN>
8</SPAN>	Golf</SPAN>	lai.234.l2h</SPAN>	own.481.a7g</SPAN>	wkd.789.d1o</SPAN>	wnp.180.g3f</SPAN>	oqm.193.r6s</SPAN>	What is the Formula?</SPAN>
9</SPAN>	Hotel</SPAN>	aiw.491.w0h</SPAN>	odv.358.s8f</SPAN>	abc.587.v3b</SPAN>	wno.734.h5h</SPAN>	qpn.715.y2j</SPAN>	What is the Formula?</SPAN>	abc.587.v3b</SPAN>
14</SPAN>
15</SPAN>	Search String</SPAN>	abc.587</SPAN>

<TBODY>
</TBODY>

Can this be done with a formula or does it need some sort of macro? (Macro's are completely foreign to me!)

Many thanks,

 

[Inzuri]

Assuming that there are only 5 users overall, the below formula does what you're looking for I believe:

Machine	User 1	User 2	User 3	User 4	User 5	Full text of cell containing the search string	Desired Result
Alpha	ghl.792.c7x	abc.587.k2g	alt.982.g5z	qly.496.b6s	qpn.735.y1h	abc.587.k2g	abc.587.k2g
Bravo	own.731.h6x	rmx.183.w4h	wpz.388.g2s	abc.584.w6c	abc.587.w7f	abc.587.w7f	abc.587.w7f
Charlie	abc.589.w7l	alm.921.w8j	abc.587.h8e	vxn.741.w6k	qnp.552.l1c	abc.587.h8e	abc.587.h8e
Delta	arc.365.y4d	wpv.714.r2d	qpk.795.e1a	adc.587.w8h	whr.198.v5o	Missing
Echo	abc.587.l1j	pwm.735.t1s	jhm.726.w3w	mkn.723.b6b	ops.717.s2t	abc.587.l1j	abc.587.l1j
Foxtrot	asg.356.w6d	lfr.483.j4v	pfd.587.h5f	abc.587.p7t	alt.460.w8j	abc.587.p7t	abc.587.p7t
Golf	lai.234.l2h	own.481.a7g	wkd.789.d1o	wnp.180.g3f	oqm.193.r6s	Missing
Hotel	aiw.491.w0h	odv.358.s8f	abc.587.v3b	wno.734.h5h	qpn.715.y2j	abc.587.v3b	abc.587.v3b
Search String	abc.587

<tbody>
</tbody><colgroup><col span="8"></colgroup>

Machine starts in Cell A1, Row 2 is Blank, Info continues in Row 3. Search String is in cell B12.

Formula in G3: =IF(LEFT(B3,7)=$B$12,B3,IF(LEFT(C3,7)=$B$12,C3,IF(LEFT(D3,7)=$B$12,D3,IF(LEFT(E3,7)=$B$12,E3,IF(LEFT(F3,7)=$B$12,F3,"Missing")))))

Copy down to cover all rows. This also adds "Missing" if the string is not seen in the row. There is most likely a better way to do this, but this is what I was able to come up with.

 

[Firefly2012]

Here's one way:

Excel 2010
	A	B	C	D	E	F	G	H
1	Machine	User 1	User 2	User 3	User 4	User 5	Full text of cell containing	Desired result in
2							the Search String	column G
3	Alpha	ghl.792.c7x	abc.587.k2g	alt.982.g5z	qly.496.b6s	qpn.735.y1h	abc.587.k2g	abc.587.k2g
4	Bravo	own.731.h6x	rmx.183.w4h	wpz.388.g2s	abc.584.w6c	abc.587.w7f	abc.587.w7f	abc.587.w7f
5	Charlie	abc.589.w7l	alm.921.w8j	abc.587.h8e	vxn.741.w6k	qnp.552.l1c	abc.587.h8e	abc.587.h8e
6	Delta	arc.365.y4d	wpv.714.r2d	qpk.795.e1a	adc.587.w8h	whr.198.v5o
7	Echo	abc.587.l1j	pwm.735.t1s	jhm.726.w3w	mkn.723.b6b	ops.717.s2t	abc.587.l1j	abc.587.l1j
8	Foxtrot	asg.356.w6d	lfr.483.j4v	pfd.587.h5f	abc.587.p7t	alt.460.w8j	abc.587.p7t	abc.587.p7t
9	Golf	lai.234.l2h	own.481.a7g	wkd.789.d1o	wnp.180.g3f	oqm.193.r6s
10	Hotel	aiw.491.w0h	odv.358.s8f	abc.587.v3b	wno.734.h5h	qpn.715.y2j	abc.587.v3b	abc.587.v3b
11
12	Search String	abc.587
Sheet3

Cell Formulas
Range	Formula
G3	=IFERROR(HLOOKUP("*"&$B$12&"*",$B3:$F3,1,0),"")

 

[BoyScout]

Many thanks Inzuri. That works a treat!

 

[BoyScout]

Thanks Firefly2012. That looks like the sort of thing I was expecting, but unfortunately I get a '#NAME?' error when I try this. I am using XP and Excel 2003. I'm not sure if that makes a difference? - I can't find IFERROR in the function list, so I guess that's the reason.

 

[Firefly2012]

Yes - IFERROR is only available from xl2007+

Instead you could use:

=IF(ISNA(HLOOKUP("*"&$B$12&"*",$B3:$F3,1,0)),"",HLOOKUP("*"&$B$12&"*",$B3:$F3,1,0))

 

[BoyScout]

That's got it! Many thanks to you both for your help. Much appreciated.

 

[Firefly2012]

You're welcome