Extract data from table based on symbol and return the value from column.

[nredman]

Hi Experts,

We are trying to extract data based on the "symbol" for example find all "CNU" and return the "C" value. i.e. for CNU 3.44 into separate columns (figure 2) for all the symbols.

Is this possible?

Thank you

Figure 1

Symbol	N	D	T	O	H	L	C	V	OI
CNU		29-Jan-16	0	3.41	3.45	3.4	3.44	112474	0
MTU		29-Jan-16	0	12.45	12.8	12.41	12.58	3879420	0
SPK		29-Jan-16	0	3.08	3.1	2.99	3.1	2373568	0
TPM		29-Jan-16	0	9.9	10.13	9.895	10.05	1309932	0
TLS		29-Jan-16	0	5.57	5.63	5.57	5.63	27231040	0
VOC		29-Jan-16	0	7.8	8	7.75	7.86	5497848	0
SPK		28-Jan-16	0	3	3.08	2.99	3.06	1141232	0
CNU		28-Jan-16	0	3.42	3.425	3.36	3.41	148818	0
MTU		28-Jan-16	0	11.77	12.2	11.69	12.1	1437747	0
TPM		28-Jan-16	0	9.77	9.95	9.7	9.88	1226355	0
TLS		28-Jan-16	0	5.51	5.62	5.49	5.59	28126660	0
VOC		28-Jan-16	0	7.3	7.58	7.25	7.5	2731219	0
CNU		27-Jan-16	0	3.43	3.55	3.42	3.44	171871	0
SPK		27-Jan-16	0	3.05	3.08	3	3.01	819206	0
MTU		27-Jan-16	0	11.55	11.8	11.47	11.78	1394994	0
TLS		27-Jan-16	0	5.53	5.54	5.5	5.53	19720440	0
VOC		27-Jan-16	0	7.2	7.38	7.19	7.33	1421436	0
TPM		27-Jan-16	0	9.99	10.025	9.75	9.78	1462942	0
MTU		25-Jan-16	0	11.43	11.61	11.43	11.59	858948	0
CNU		25-Jan-16	0	3.43	3.43	3.39	3.39	85512	0

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

Figure 2

cnu	mtu
3.44	12.58
3.41	12.1
3.44	11.78

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

3.39

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this formula. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across & down.

Excel Workbook
	A	H	K	L	M	N	O	P	Q	R
1	Symbol	C			CNU	MTU	SPK	TPM	TLS	VOC
2	CNU	3.44			3.44	12.58	3.1	10.05	5.63	7.86
3	MTU	12.58			3.41	12.1	3.06	9.88	5.59	7.5
4	SPK	3.1			3.44	11.78	3.01	9.78	5.53	7.33
5	TPM	10.05			3.39	11.59
6	TLS	5.63
7	VOC	7.86
8	SPK	3.06
9	CNU	3.41
10	MTU	12.1
11	TPM	9.88
12	TLS	5.59
13	VOC	7.5
14	CNU	3.44
15	SPK	3.01
16	MTU	11.78
17	TLS	5.53
18	VOC	7.33
19	TPM	9.78
20	MTU	11.59
21	CNU	3.39
List Values

 

[nredman]

Thank you Peter for your assistance and time.

 

[nredman]

Hi Peter,

I have no idea why the formula works ok with all symbols except "SPK"?

Thanks

 

[nredman]

Hi Peter,

Its all good, thanks again.

 

[Peter_SSs]

Cheers. Glad you got it sorted.