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


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



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