Extract only specific 2 numbers

[motilulla]

Hello,

From my result column B6:F33 I want to extract only 2 numbers that are listed in the cells H2 & H3 and show them into column cells H6:H33.

*	A	B	C	D	E	F	G	H	I
1
2							num1	6
3							num2	9
4
5		n1	n2	n3	n4	n5		Result
6		16	29	32	36	41
7		7	13	6	9	50		6 | 9
8		14	18	19	31	37
9		4	7	33	37	39
10		1	6	8	9	47		6 | 9
11		33	36	37	42	45
12		3	4	10	23	43
13		4	12	24	27	36
14		1	4	10	19	23
15		14	15	28	35	40
16		6	7	8	9	49		6 | 9
17		5	6	16	23	27		6
18		15	16	21	36	38
19		1	6	9	32	39		6 | 9
20		15	29	37	39	49
21		6	7	8	9	12		6 | 9
22		9	13	34	41	42		9
23		2	7	8	10	47
24		2	23	28	40	43
25		3	21	30	34	35
26		4	23	24	28	34
27		2	3	4	5	6		6
28		24	26	31	38	50
29		7	10	27	31	34		9
30		9	10	19	37	50		6 | 9
31		5	15	24	35	44
32		20	27	41	43	50
33		6	9	10	27	35		6 | 9
34
35
36
37

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Misca]

Try something like

=TRIM(SUBSTITUTE(IF(ISNUMBER(MATCH($H$2,B6:F6,0)),$H$2&" ","")&IF(ISNUMBER(MATCH($H$3,B6:F6,0))," "&$H$3,""),"  ","|"))

 

[sandy666]

Info about Excel version should be at the top of your first post

 

[Fluff]

It doesn't have to be at the top.

 

[motilulla]

Try something like

=TRIM(SUBSTITUTE(IF(ISNUMBER(MATCH($H$2,B6:F6,0)),$H$2&" ","")&IF(ISNUMBER(MATCH($H$3,B6:F6,0))," "&$H$3,""),"  ","|"))

​

Fluff, the formula works like magic! Thank you very much for your kind help and time you spend solve it.

Good Luck to you and have a nice time.

Kind Regards,
Moti

 

[Fluff]

The formula was supplied by Misca. So that's who you should be thanking

 

[motilulla]

The formula was supplied by Misca. So that's who you should be thanking

Fluff, I am Sorry I cannot edit your name in the above post# because the edit time is over thank you for pointing me it is my mistake not reading the post correctly.

Kind Regards,
Moti

Try something like

=TRIM(SUBSTITUTE(IF(ISNUMBER(MATCH($H$2,B6:F6,0)),$H$2&" ","")&IF(ISNUMBER(MATCH($H$3,B6:F6,0))," "&$H$3,""),"  ","|"))

sandy666, the formula you supplied works like magic! Thank you very much for your kind help and time you spend solve it.​

Good Luck to you and have a nice time.

Kind Regards,
Moti

 

[Fluff]

Did you actually read my post?
The formula was not supplied by Sandy666 nor by myself, but by Misca.

 

[motilulla]

Did you actually read my post?
The formula was not supplied by Sandy666 nor by myself, but by Misca.

Hello Fluff, I am extremely sorry as this has site been developed new I got confuse reading and replying post correctly I think I got jump to another post and pick the name wrongly I apologise for my mistake

Kind Regards,
Moti ?

Try something like

=TRIM(SUBSTITUTE(IF(ISNUMBER(MATCH($H$2,B6:F6,0)),$H$2&" ","")&IF(ISNUMBER(MATCH($H$3,B6:F6,0))," "&$H$3,""),"  ","|"))

Misca, the formula you supplied works like magic! Thank you very much for your kind help and time you spend solve it.

I am very sorry about the confusion I have created.

Good Luck to you and have a nice time.

Kind Regards,
Moti

 

[Rick Rothstein]

Here is a simpler formula that should also work for you...

=MID(IF(COUNTIF(B6:F6,H$2)," | "&H$2,"")&IF(COUNTIF(B6:F6,H$3)," | "&H$3,""),4,99)