IF isnumber and search funtion

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
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)
 

Attachments

  • Query 2.png
    Query 2.png
    45.3 KB · Views: 13
Sort by date Sort by votes
RAJESH1960 said:
=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
ABCDEFG
1LineDESCRIPTIONTYPEANSWERMY ACCOUNTS
2100400021000024880 To : 004000550000027010 S37619917 S37713671ReceiptContra27010Contra
3200400055000027010 To : 004000040000010310 S21651085 S21701269PaymentContra24880Contra
4300400055000027010 To : 004000040000010310 S44718572 S44737529PaymentContra10310Contra
5400400055000027010 To : 004000040000010310 S701466 S1325421PaymentContra24350Contra
6500400055000027010 To : 0040000400017794 S28638584 S28666428PaymentPaymentPayment
7600400055000027010 To : 00400021000024880 S18487986 S18960977PaymentContraContra
8700400055000027010 To : 00400021000024880 S19659009 S20043975PaymentContraContra
9800400055000027010 To : 00400021000024880 S20145180 S21200445PaymentContraContra
10900400055000027010 To : 0040000400006497 S21614497 S21626834PaymentPaymentPayment
111000400055000027010 To : 0040000400006497 S35919089 S35944589PaymentPaymentPayment
121400400055000027010 To : 00400004000024350 S62051922 S62074670PaymentContraContra
131500400055000027010 To : 00400004000024350 S80244419 S80297918PaymentContraContra
141600400055000027010 To : 00400004000024350 S81929291 S83148077PaymentContraContra
151739110021000012345 To : 00400055000027010 S24961461 S25692368ReceiptContraContra
161839110021000012345 To : 00400055000027010 S25321639 S27240183ReceiptContraContra
171939110021000012345 To : 00400055000027010 S42377353 S42850086ReceiptContraContra
182039110021000012345 To : 00400055000027010 S48211167 S49452768ReceiptContraContra
192139110021000012345 To : 00400055000012345 S70685132 S72972466ReceiptReceiptReceipt
202239110021000012345 To : 00400055000012345 S9482650 S9677308ReceiptReceiptReceipt
21
Sheet1
Cell Formulas
RangeFormula
G2:G20G2=IF(ISNUMBER(IFERROR(SEARCH($F$2:$F$5,B2),0)),E2,D2)
Click to expand...
Line 15 to 19 is not in the criteria. I need an exact match of 2 account numbers. Do I need an "and" "or" function within the formula...?
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
RAJESH1960 said:
=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
ABCDEFG
1LineDESCRIPTIONTYPEANSWERMY ACCOUNTS
2100400021000024880 To : 004000550000027010 S37619917 S37713671ReceiptContra27010Contra
3200400055000027010 To : 004000040000010310 S21651085 S21701269PaymentContra24880Contra
4300400055000027010 To : 004000040000010310 S44718572 S44737529PaymentContra10310Contra
5400400055000027010 To : 004000040000010310 S701466 S1325421PaymentContra24350Contra
6500400055000027010 To : 0040000400017794 S28638584 S28666428PaymentPaymentPayment
7600400055000027010 To : 00400021000024880 S18487986 S18960977PaymentContraContra
8700400055000027010 To : 00400021000024880 S19659009 S20043975PaymentContraContra
9800400055000027010 To : 00400021000024880 S20145180 S21200445PaymentContraContra
10900400055000027010 To : 0040000400006497 S21614497 S21626834PaymentPaymentPayment
111000400055000027010 To : 0040000400006497 S35919089 S35944589PaymentPaymentPayment
121400400055000027010 To : 00400004000024350 S62051922 S62074670PaymentContraContra
131500400055000027010 To : 00400004000024350 S80244419 S80297918PaymentContraContra
141600400055000027010 To : 00400004000024350 S81929291 S83148077PaymentContraContra
151739110021000012345 To : 00400055000027010 S24961461 S25692368ReceiptContraContra
161839110021000012345 To : 00400055000027010 S25321639 S27240183ReceiptContraContra
171939110021000012345 To : 00400055000027010 S42377353 S42850086ReceiptContraContra
182039110021000012345 To : 00400055000027010 S48211167 S49452768ReceiptContraContra
192139110021000012345 To : 00400055000012345 S70685132 S72972466ReceiptReceiptReceipt
202239110021000012345 To : 00400055000012345 S9482650 S9677308ReceiptReceiptReceipt
21
Sheet1
Cell Formulas
RangeFormula
G2:G20G2=IF(ISNUMBER(IFERROR(SEARCH($F$2:$F$5,B2),0)),E2,D2)
Click to expand...
Sorry, My original formula is =IF(ISNUMBER(IFERROR(SEARCH($F$2:$F$5,B2),0)),E2,"Contra")
This needs to be corrected.
 
Upvote 0
RAJESH1960 said:
=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
ABCDEFG
1LineDESCRIPTIONTYPEANSWERMY ACCOUNTS
2100400021000024880 To : 004000550000027010 S37619917 S37713671ReceiptContra27010Contra
3200400055000027010 To : 004000040000010310 S21651085 S21701269PaymentContra24880Contra
4300400055000027010 To : 004000040000010310 S44718572 S44737529PaymentContra10310Contra
5400400055000027010 To : 004000040000010310 S701466 S1325421PaymentContra24350Contra
6500400055000027010 To : 0040000400017794 S28638584 S28666428PaymentPaymentPayment
7600400055000027010 To : 00400021000024880 S18487986 S18960977PaymentContraContra
8700400055000027010 To : 00400021000024880 S19659009 S20043975PaymentContraContra
9800400055000027010 To : 00400021000024880 S20145180 S21200445PaymentContraContra
10900400055000027010 To : 0040000400006497 S21614497 S21626834PaymentPaymentPayment
111000400055000027010 To : 0040000400006497 S35919089 S35944589PaymentPaymentPayment
121400400055000027010 To : 00400004000024350 S62051922 S62074670PaymentContraContra
131500400055000027010 To : 00400004000024350 S80244419 S80297918PaymentContraContra
141600400055000027010 To : 00400004000024350 S81929291 S83148077PaymentContraContra
151739110021000012345 To : 00400055000027010 S24961461 S25692368ReceiptContraContra
161839110021000012345 To : 00400055000027010 S25321639 S27240183ReceiptContraContra
171939110021000012345 To : 00400055000027010 S42377353 S42850086ReceiptContraContra
182039110021000012345 To : 00400055000027010 S48211167 S49452768ReceiptContraContra
192139110021000012345 To : 00400055000012345 S70685132 S72972466ReceiptReceiptReceipt
202239110021000012345 To : 00400055000012345 S9482650 S9677308ReceiptReceiptReceipt
21
Sheet1
Cell Formulas
RangeFormula
G2:G20G2=IF(ISNUMBER(IFERROR(SEARCH($F$2:$F$5,B2),0)),E2,D2)
Click to expand...
Sorry again, Can I delete the question and post the same question in a different sheet. I got it all wrong
 
Upvote 0
Query.xlsx
ABCDEFG
1LineDESCRIPTIONTYPEData to MatchFormula UsedEXPECTED ANSWER
2100400021000024880 To : 004000550000027010 S37619917 S37713671Receipt27010ReceiptContra
3200400055000027010 To : 004000040000010310 S21651085 S21701269Payment24880ContraContra
4300400055000027010 To : 004000040000010310 S44718572 S44737529Payment10310PaymentContra
5400400055000027010 To : 004000040000010310 S701466 S1325421Payment24350ContraContra
6500400055000027010 To : 0040000400017794 S28638584 S28666428PaymentCASHContraPayment
7600400055000027010 To : 00400021000024880 S18487986 S18960977PaymentContraContra
8700400055000027010 To : 00400021000024880 S19659009 S20043975PaymentContraContra
9800400055000027010 To : 00400021000024880 S20145180 S21200445PaymentContraContra
10900400055000027010 To : 0040000400006497 S21614497 S21626834PaymentContraPayment
111000400055000027010 To : 0040000400006497 S35919089 S35944589PaymentContraPayment
121400400055000027010 To : 00400004000024350 S62051922 S62074670PaymentContraContra
131500400055000027010 To : 00400004000024350 S80244419 S80297918PaymentContraContra
141600400055000027010 To : 00400004000024350 S81929291 S83148077PaymentContraContra
151739110021000012345 To : 00400055000027010 S24961461 S25692368ReceiptContraReceipt
161839110021000012345 To : 00400055000027010 S25321639 S27240183ReceiptContraReceipt
1719BY CASHS12100882M738900ReceiptContraContra
1820BY CASHS19221212M597294ReceiptContraContra
192139110021000012345 To : 00400055000012345 S70685132 S72972466ReceiptContraReceipt
202239110021000012345 To : 00400055000012345 S9482650 S9677308ReceiptContraReceipt
21
22In the description column, if it contains any of the contents in column F2:F6, then Column G should Display Contra else Column D
23
24
Sheet1
Cell Formulas
RangeFormula
E2:E20E2=IF(IFERROR(SEARCH($D$2:$D$6,B2),0),C2,"Contra")
 
Upvote 0
RAJESH1960 said:
Query.xlsx
ABCDEFG
1LineDESCRIPTIONTYPEData to MatchFormula UsedEXPECTED ANSWER
2100400021000024880 To : 004000550000027010 S37619917 S37713671Receipt27010ReceiptContra
3200400055000027010 To : 004000040000010310 S21651085 S21701269Payment24880ContraContra
4300400055000027010 To : 004000040000010310 S44718572 S44737529Payment10310PaymentContra
5400400055000027010 To : 004000040000010310 S701466 S1325421Payment24350ContraContra
6500400055000027010 To : 0040000400017794 S28638584 S28666428PaymentCASHContraPayment
7600400055000027010 To : 00400021000024880 S18487986 S18960977PaymentContraContra
8700400055000027010 To : 00400021000024880 S19659009 S20043975PaymentContraContra
9800400055000027010 To : 00400021000024880 S20145180 S21200445PaymentContraContra
10900400055000027010 To : 0040000400006497 S21614497 S21626834PaymentContraPayment
111000400055000027010 To : 0040000400006497 S35919089 S35944589PaymentContraPayment
121400400055000027010 To : 00400004000024350 S62051922 S62074670PaymentContraContra
131500400055000027010 To : 00400004000024350 S80244419 S80297918PaymentContraContra
141600400055000027010 To : 00400004000024350 S81929291 S83148077PaymentContraContra
151739110021000012345 To : 00400055000027010 S24961461 S25692368ReceiptContraReceipt
161839110021000012345 To : 00400055000027010 S25321639 S27240183ReceiptContraReceipt
1719BY CASHS12100882M738900ReceiptContraContra
1820BY CASHS19221212M597294ReceiptContraContra
192139110021000012345 To : 00400055000012345 S70685132 S72972466ReceiptContraReceipt
202239110021000012345 To : 00400055000012345 S9482650 S9677308ReceiptContraReceipt
21
22In the description column, if it contains any of the contents in column F2:F6, then Column G should Display Contra else Column D
23
24
Sheet1
Cell Formulas
RangeFormula
E2:E20E2=IF(IFERROR(SEARCH($D$2:$D$6,B2),0),C2,"Contra")
Click to expand...
With the help of a formula, the column E should display the answer which is displayed in column G,
 
Upvote 0
Your expected answers in post#14 do not make any sense & (IMO) are completely wrong.
Going on your data & formula from post#8 I get the correct result. You may need to confirm the formula with Ctrl Shift Enter & then copy down.
 
Upvote 0
Fluff said:
Your expected answers in post#14 do not make any sense & (IMO) are completely wrong.
Going on your data & formula from post#8 I get the correct result. You may need to confirm the formula with Ctrl Shift Enter & then copy down.
Click to expand...
The query I posted earlier was all wrong. I have tried many formulas still I am not getting the right solution. If you don't mind Please refer this sheet. Sorry for the mix up.
Query.xlsx
ABCDEFGHI
1LineDESCRIPTIONTYPEData to MatchEnter Correct Formula HereEXPECTED ANSWERTest 1test 2
2100400021000024880 To : 004000550000027010 S37619917 S37713671Receipt27010ContraContraContra
3200400055000027010 To : 004000040000010310 S21651085 S21701269Payment24880ContraPaymentPayment
4300400055000027010 To : 004000040000010310 S44718572 S44737529Payment10310ContraContraContra
5400400055000027010 To : 004000040000010310 S701466 S1325421Payment24350ContraPaymentPayment
6500400055000027010 To : 0040000400017794 S28638584 S28666428PaymentCASHPaymentPaymentPayment
7600400055000027010 To : 00400021000024880 S18487986 S18960977PaymentContraPaymentPayment
8700400055000027010 To : 00400021000024880 S19659009 S20043975PaymentContraPaymentPayment
9900400055000027010 To : 0040000400006497 S21614497 S21626834PaymentPaymentPaymentPayment
101000400055000027010 To : 0040000400006497 S35919089 S35944589PaymentPaymentPaymentPayment
111400400055000027010 To : 00400004000024350 S62051922 S62074670PaymentContraPaymentPayment
121600400055000027010 To : 00400004000024350 S81929291 S83148077PaymentContraPaymentPayment
131739110021000012345 To : 00400055000027010 S24961461 S25692368ReceiptReceiptReceiptReceipt
141839110021000012345 To : 00400055000027010 S25321639 S27240183ReceiptReceiptReceiptReceipt
1519BY CASHS12100882M738900ReceiptContraReceiptContra
1620BY CASHS19221212M597294ReceiptContraReceiptContra
172139110021000012345 To : 00400055000012345 S70685132 S72972466ReceiptReceiptReceiptReceipt
182239110021000012345 To : 00400055000012345 S9482650 S9677308ReceiptReceiptReceiptReceipt
19
20In the description column, if it contains any 2 of the contents in column F2:F6, then Column E should Display Contra else Column D
21Sir, Please help me with the correct formula in column E
22Formula used: =IF((IFERROR(SEARCH($D$2:$D$6,B2),0)),"Contra",C2)Not correct
23again tried with =IF(OR(ISNUMBER(SEARCH($D$2:$D$5,B2)),ISNUMBER(SEARCH($D$6,B2))),"Contra",C2)partly correct
24
Sheet1
Cell Formulas
RangeFormula
G2:G18G2=IF((IFERROR(FIND($D$2:$D$6,B2),0)),"Contra",C2)
H2:H18H2=IF(OR(ISNUMBER(SEARCH($D$2:$D$5,B2)),ISNUMBER(SEARCH($D$6,B2))),"Contra",C2)
 
Upvote 0
Two things.
1) you are not using the formula I suggested.
2) your expected answers make absolutely no sense whatsoever. Only rows 17 & 18 should be col C the rest should all be contra.
 
Upvote 0
Fluff said:
How about
Excel Formula: 
=IF(SUM(IFERROR(SEARCH($F$2:$F$5,B2),0)),E2,D2)
Fluff said:
Two things.
1) you are not using the formula I suggested.
2) your expected answers make absolutely no sense whatsoever. Only rows 17 & 18 should be col C the rest should all be contra.
Click to expand...
I have to write the formula in cell E2 and copy it down to get the solution. How can I use E2 in the formula. And the range to match is F2:F6 and not F2:F5. Refer only the last image sent as the previous ones were wrong. Will discuss tomorrow. Time to get some sleep. Good Night
Click to expand...
 
Upvote 0
RAJESH1960 said:
=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
ABCDEFG
1LineDESCRIPTIONTYPEANSWERMY ACCOUNTS
2100400021000024880 To : 004000550000027010 S37619917 S37713671ReceiptContra27010Contra
3200400055000027010 To : 004000040000010310 S21651085 S21701269PaymentContra24880Contra
4300400055000027010 To : 004000040000010310 S44718572 S44737529PaymentContra10310Contra
5400400055000027010 To : 004000040000010310 S701466 S1325421PaymentContra24350Contra
6500400055000027010 To : 0040000400017794 S28638584 S28666428PaymentPaymentPayment
7600400055000027010 To : 00400021000024880 S18487986 S18960977PaymentContraContra
8700400055000027010 To : 00400021000024880 S19659009 S20043975PaymentContraContra
9800400055000027010 To : 00400021000024880 S20145180 S21200445PaymentContraContra
10900400055000027010 To : 0040000400006497 S21614497 S21626834PaymentPaymentPayment
111000400055000027010 To : 0040000400006497 S35919089 S35944589PaymentPaymentPayment
121400400055000027010 To : 00400004000024350 S62051922 S62074670PaymentContraContra
131500400055000027010 To : 00400004000024350 S80244419 S80297918PaymentContraContra
141600400055000027010 To : 00400004000024350 S81929291 S83148077PaymentContraContra
151739110021000012345 To : 00400055000027010 S24961461 S25692368ReceiptContraContra
161839110021000012345 To : 00400055000027010 S25321639 S27240183ReceiptContraContra
171939110021000012345 To : 00400055000027010 S42377353 S42850086ReceiptContraContra
182039110021000012345 To : 00400055000027010 S48211167 S49452768ReceiptContraContra
192139110021000012345 To : 00400055000012345 S70685132 S72972466ReceiptReceiptReceipt
202239110021000012345 To : 00400055000012345 S9482650 S9677308ReceiptReceiptReceipt
21
Sheet1
Cell Formulas
RangeFormula
G2:G20G2=IF(ISNUMBER(IFERROR(SEARCH($F$2:$F$5,B2),0)),E2,D2)
Click to expand...
Was exhausted after working for 36 hours. So I couldn't post the query correctly. Now after a good sleep I am refreshed and here is my question once again.
Query sent to Md.Ismail.xlsx
ABCDEFGH
1LineDESCRIPTIONTYPEData to MatchEnter Correct Formula Here
2100400021000024880 To : 004000550000027010 S37619917 S37713671Receipt27010
3200400055000027010 To : 004000040000010310 S21651085 S21701269Payment24880
4300400055000027010 To : 004000040000010310 S44718572 S44737529Payment10310
5400400055000027010 To : 004000040000010310 S701466 S1325421Payment24350
6500400055000027010 To : 0040000400017794 S28638584 S28666428PaymentCASH
7600400055000027010 To : 00400021000024880 S18487986 S18960977Payment
8700400055000027010 To : 00400021000024880 S19659009 S20043975Payment
9900400055000027010 To : 0040000400006497 S21614497 S21626834Payment
101000400055000027010 To : 0040000400006497 S35919089 S35944589Payment
111400400055000027010 To : 00400004000024350 S62051922 S62074670Payment
121600400055000027010 To : 00400004000024350 S81929291 S83148077Payment
131739110021000012345 To : 00400055000027010 S24961461 S25692368Receipt
141839110021000012345 To : 00400055000027010 S25321639 S27240183Receipt
1519BY CASHS12100882M738900Receipt
1620BY CASHS19221212M597294Receipt
172139110021000012345 To : 00400055000012345 S70685132 S72972466Receipt
182239110021000012345 To : 00400055000012345 S9482650 S9677308Receipt
19
202 conditions to be met to get the result in Column E -
211. The contents of column B should match minimum 2 contents with column $D$2:$D$6, then Column E should Display Contra else Column D
222. Also in column B2, if it displays "CASH" ond doesn't match with the other contents in $D$2:$D$6, then too Column E should Display Contra
23
24
Query 58

Fluff said:
Your expected answers in post#14 do not make any sense & (IMO) are completely wrong.
Going on your data & formula from post#8 I get the correct result. You may need to confirm the formula with Ctrl Shift Enter & then copy down.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Condense formula
Replies
8
Views
372
SanjayGMusafir
SanjayGMusafir
D
  • Question
Multiple conditions
Replies
8
Views
192
awoohaw
awoohaw
J
  • Question
If And formula not working
Replies
7
Views
238
KRice
K
N
  • Solved
Function to find a portion of text in a cell
Replies
5
Views
488
Fluff
Fluff
A
  • Solved
Searching for 2 different values in the same cell in a different tab
2
Replies
14
Views
191
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,990
Messages
6,122,626
Members
449,094
Latest member
bsb1122

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top