IF isnumber and search funtion

[RAJESH1960]

Please help me to correct this formula. The cells marked Yellow in column B should display Contra in column G. I am getting correct answer in some cells, but not all. I have used this function: =IF(ISNUMBER(SEARCH($F$2:$F$5,B2)),E2,D2)

 

[Fluff]

How about

=IF(SUM(IFERROR(SEARCH($F$2:$F$5,B2),0)),E2,D2)

 

[alansidman]

Cannot manipulate data in a picture. Please use XL2BB to upload your sample data.

 

[kweaver]

I believe the problem is that it's finding a blank (space) and that's a number.

 

[RAJESH1960]

How about

=IF(SUM(IFERROR(SEARCH($F$2:$F$5,B2),0)),E2,D2)

Only 2 entries are matching with your formula, same result with my formula too. . But it has to match 10 entries and show contra

 

[Fluff]

In that case please post some sample data using the XL2BB add-in.

 

[RAJESH1960]

In that case please post some sample data using the XL2BB add-in.

I have tried many times but I am not able to upload. Do you have any link of a video how to upload a xl2bb file here.? I always get a message that "Table information successfully saved in the clipboard. You can paste it in the forum post".

 

[RAJESH1960]

Query.xlsx
	A	B	C	D	E	F	G	H
1	Line	DESCRIPTION		TYPE	ANSWER	MY ACCOUNTS
2	1	00400021000024880 To : 004000550000027010 S37619917 S37713671		Receipt	Contra	27010	Contra
3	2	00400055000027010 To : 004000040000010310 S21651085 S21701269		Payment	Contra	24880	Payment
4	3	00400055000027010 To : 004000040000010310 S44718572 S44737529		Payment	Contra	10310	Contra
5	4	00400055000027010 To : 004000040000010310 S701466 S1325421		Payment	Contra	24350	Payment
6	5	00400055000027010 To : 0040000400017794 S28638584 S28666428		Payment	Payment		Payment
7	6	00400055000027010 To : 00400021000024880 S18487986 S18960977		Payment	Contra		Payment
8	7	00400055000027010 To : 00400021000024880 S19659009 S20043975		Payment	Contra		Payment
9	8	00400055000027010 To : 00400021000024880 S20145180 S21200445		Payment	Contra		Payment
10	9	00400055000027010 To : 0040000400006497 S21614497 S21626834		Payment	Payment		Payment
11	10	00400055000027010 To : 0040000400006497 S35919089 S35944589		Payment	Payment		Payment
12	14	00400055000027010 To : 00400004000024350 S62051922 S62074670		Payment	Contra		Payment
13	15	00400055000027010 To : 00400004000024350 S80244419 S80297918		Payment	Contra		Payment
14	16	00400055000027010 To : 00400004000024350 S81929291 S83148077		Payment	Contra		Payment
15	17	39110021000012345 To : 00400055000027010 S24961461 S25692368		Receipt	Contra		Receipt
16	18	39110021000012345 To : 00400055000027010 S25321639 S27240183		Receipt	Contra		Receipt
17	19	39110021000012345 To : 00400055000027010 S42377353 S42850086		Receipt	Contra		Receipt
18	20	39110021000012345 To : 00400055000027010 S48211167 S49452768		Receipt	Contra		Receipt
19	21	39110021000012345 To : 00400055000012345 S70685132 S72972466		Receipt	Receipt		Receipt
20	22	39110021000012345 To : 00400055000012345 S9482650 S9677308		Receipt	Receipt		Receipt
21
Sheet1

Cell Formulas
Range	Formula
G2:G20	G2	=IF(SUM(IFERROR(SEARCH($F$2:$F$5,B2),0)),E2,D2)

 

[RAJESH1960]

Query.xlsx
	A	B	C	D	E	F	G	H
1	Line	DESCRIPTION		TYPE	ANSWER	MY ACCOUNTS
2	1	00400021000024880 To : 004000550000027010 S37619917 S37713671		Receipt	Contra	27010	Contra
3	2	00400055000027010 To : 004000040000010310 S21651085 S21701269		Payment	Contra	24880	Payment
4	3	00400055000027010 To : 004000040000010310 S44718572 S44737529		Payment	Contra	10310	Contra
5	4	00400055000027010 To : 004000040000010310 S701466 S1325421		Payment	Contra	24350	Payment
6	5	00400055000027010 To : 0040000400017794 S28638584 S28666428		Payment	Payment		Payment
7	6	00400055000027010 To : 00400021000024880 S18487986 S18960977		Payment	Contra		Payment
8	7	00400055000027010 To : 00400021000024880 S19659009 S20043975		Payment	Contra		Payment
9	8	00400055000027010 To : 00400021000024880 S20145180 S21200445		Payment	Contra		Payment
10	9	00400055000027010 To : 0040000400006497 S21614497 S21626834		Payment	Payment		Payment
11	10	00400055000027010 To : 0040000400006497 S35919089 S35944589		Payment	Payment		Payment
12	14	00400055000027010 To : 00400004000024350 S62051922 S62074670		Payment	Contra		Payment
13	15	00400055000027010 To : 00400004000024350 S80244419 S80297918		Payment	Contra		Payment
14	16	00400055000027010 To : 00400004000024350 S81929291 S83148077		Payment	Contra		Payment
15	17	39110021000012345 To : 00400055000027010 S24961461 S25692368		Receipt	Contra		Receipt
16	18	39110021000012345 To : 00400055000027010 S25321639 S27240183		Receipt	Contra		Receipt
17	19	39110021000012345 To : 00400055000027010 S42377353 S42850086		Receipt	Contra		Receipt
18	20	39110021000012345 To : 00400055000027010 S48211167 S49452768		Receipt	Contra		Receipt
19	21	39110021000012345 To : 00400055000012345 S70685132 S72972466		Receipt	Receipt		Receipt
20	22	39110021000012345 To : 00400055000012345 S9482650 S9677308		Receipt	Receipt		Receipt
21
Sheet1

Cell Formulas
Range	Formula
G2:G20	G2	=IF(SUM(IFERROR(SEARCH($F$2:$F$5,B2),0)),E2,D2)

I never knew it was so easy to upload a XL2BB File...

 

[RAJESH1960]

=IF(ISNUMBER(IFERROR(SEARCH($F$2:$F$5,B15),0)),E15,D15)
with this formula I am getting the contra entries but with some additional ones also.

Query.xlsx
	A	B	C	D	E	F	G
1	Line	DESCRIPTION		TYPE	ANSWER	MY ACCOUNTS
2	1	00400021000024880 To : 004000550000027010 S37619917 S37713671		Receipt	Contra	27010	Contra
3	2	00400055000027010 To : 004000040000010310 S21651085 S21701269		Payment	Contra	24880	Contra
4	3	00400055000027010 To : 004000040000010310 S44718572 S44737529		Payment	Contra	10310	Contra
5	4	00400055000027010 To : 004000040000010310 S701466 S1325421		Payment	Contra	24350	Contra
6	5	00400055000027010 To : 0040000400017794 S28638584 S28666428		Payment	Payment		Payment
7	6	00400055000027010 To : 00400021000024880 S18487986 S18960977		Payment	Contra		Contra
8	7	00400055000027010 To : 00400021000024880 S19659009 S20043975		Payment	Contra		Contra
9	8	00400055000027010 To : 00400021000024880 S20145180 S21200445		Payment	Contra		Contra
10	9	00400055000027010 To : 0040000400006497 S21614497 S21626834		Payment	Payment		Payment
11	10	00400055000027010 To : 0040000400006497 S35919089 S35944589		Payment	Payment		Payment
12	14	00400055000027010 To : 00400004000024350 S62051922 S62074670		Payment	Contra		Contra
13	15	00400055000027010 To : 00400004000024350 S80244419 S80297918		Payment	Contra		Contra
14	16	00400055000027010 To : 00400004000024350 S81929291 S83148077		Payment	Contra		Contra
15	17	39110021000012345 To : 00400055000027010 S24961461 S25692368		Receipt	Contra		Contra
16	18	39110021000012345 To : 00400055000027010 S25321639 S27240183		Receipt	Contra		Contra
17	19	39110021000012345 To : 00400055000027010 S42377353 S42850086		Receipt	Contra		Contra
18	20	39110021000012345 To : 00400055000027010 S48211167 S49452768		Receipt	Contra		Contra
19	21	39110021000012345 To : 00400055000012345 S70685132 S72972466		Receipt	Receipt		Receipt
20	22	39110021000012345 To : 00400055000012345 S9482650 S9677308		Receipt	Receipt		Receipt
21
Sheet1

Cell Formulas
Range	Formula
G2:G20	G2	=IF(ISNUMBER(IFERROR(SEARCH($F$2:$F$5,B2),0)),E2,D2)