Multiple Search and Check in Excel

[calvinc123]

Hello I am trying to clean up a table to crosscheck some values where it is wrong. I am trying to write a formula that will capture all that I need. I have attempted the Countif function, but was unsuccessful. Here is what I am trying to do.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Account Name[/TD]
[TD]Taxation Status[/TD]
[/TR]
[TR]
[TD]John & Jill Sample IRA[/TD]
[TD]Taxable[/TD]
[/TR]
[TR]
[TD]Jake Family Trust[/TD]
[TD]Taxable[/TD]
[/TR]
[TR]
[TD]Jim Sample Roth IRA[/TD]
[TD]Taxable[/TD]
[/TR]
</tbody>[/TABLE]

The Taxation Status column is wrong for these different sample accounts. All IRAs need to be Tax Deferred, All Trusts need to be Taxable, All Roth IRAs need to be Non Taxable. I am trying to write a few formulas that will look up the information in Account Name column and based on a few key word such as "IRA" Roth IRA" or "Trust" and then cross check that with what is in the Taxation Status column. I would then like to change the status of the Taxation Status to parameters such as IRA = Tax Deferred, Trust = Taxable, Roth IRA = Non Taxable (as mentioned above). I know this is probably pretty simple, but you help is needed. Thanks!

 

[Fishboy]

Hello I am trying to clean up a table to crosscheck some values where it is wrong. I am trying to write a formula that will capture all that I need. I have attempted the Countif function, but was unsuccessful. Here is what I am trying to do.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Account Name[/TD]
[TD]Taxation Status[/TD]
[/TR]
[TR]
[TD]John & Jill Sample IRA[/TD]
[TD]Taxable[/TD]
[/TR]
[TR]
[TD]Jake Family Trust[/TD]
[TD]Taxable[/TD]
[/TR]
[TR]
[TD]Jim Sample Roth IRA[/TD]
[TD]Taxable[/TD]
[/TR]
</tbody>[/TABLE]

The Taxation Status column is wrong for these different sample accounts. All IRAs need to be Tax Deferred, All Trusts need to be Taxable, All Roth IRAs need to be Non Taxable. I am trying to write a few formulas that will look up the information in Account Name column and based on a few key word such as "IRA" Roth IRA" or "Trust" and then cross check that with what is in the Taxation Status column. I would then like to change the status of the Taxation Status to parameters such as IRA = Tax Deferred, Trust = Taxable, Roth IRA = Non Taxable (as mentioned above). I know this is probably pretty simple, but you help is needed. Thanks!

Hi Calvin, welcome to the boards.

Based on your sample information (and assuming for the sake of argument that your Account Name header is in A1) the following formula could be used and drag filled down the length of column C:

Excel 2010

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Account Name[/TD]
[TD="align: center"]Taxation Status[/TD]
[TD="align: center"]Outcome[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]John & Jill Sample IRA[/TD]
[TD="align: center"]Taxable[/TD]
[TD="align: center"]Tax Deferred[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Jake Family Trust[/TD]
[TD="align: center"]Taxable[/TD]
[TD="align: center"]Taxable[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Jim Sample Roth IRA[/TD]
[TD="align: center"]Taxable[/TD]
[TD="align: center"]Non Taxable[/TD]

</tbody>

Sheet1​

=IF(RIGHT(A2,8)="Roth IRA","Non Taxable",IF(RIGHT(A2,5)="Trust","Taxable","Tax Deferred"))

Now, this assumes that IRA, Trust or Roth IRA are always the last parts of the string in the cell. If these values are likely to be found anywhere in the cell the formulas will need to be overhauled. This also assumes that there are no other possibilities beyond the 3 outcomes you have mentioned.

Basically if the cell ends with Roth IRA the result is Non Taxable.
If the cell ends with Trust the result is Taxable.
For ALL other possibilities the result is Tax Deferred.

I'll admit this is a quick and non-flexible way of achieving the desired outcome. If any of my assumptions outlined above are incorrect then the formula will need to be reworked.

I hope this helps.

 

[calvinc123]

Thank you Fishboy!

 

[Fishboy]

You're welcome mate