IF this value IS NOT in this range THEN
 

I have three columns: Column A has a list of account numbers. Column B has a dollar amount. In column F, I have a list of accounts that I want to exclude. In Column C, I want to enter a formula that will look at the value in Column A, see if that account number is in Column F, and if it is NOT, then enter the amount in column B into column C. If the account number IS in Column F, then I want the cell in Column C to be blank. I cannot figure this out. It is something like IF(A2<$F$2:$F$21,B2,0) except you can't say "does not equal a range." I don't know the right way to say, "is not found in that range" in Excelspeak.

Can anyone help?

Answer: IF this value IS NOT in this range THEN
 

Formula for Column C:

Explanation:

1. The MATCH function looks for the value in A2 within the range $F$2:$F$21. The "0" at the end means it's looking for an exact match.
2. The ISERROR function checks if the MATCH function returns an error (i.e. if the value in A2 is not found in the range).
3. If there is an error (i.e. the value is not found), then the formula returns the value in B2.
4. If there is no error (i.e. the value is found), then the formula returns an empty string (which will display as a blank cell).

So essentially, the formula is saying "If the value in A2 is not found in the range $F$2:$F$21, then display the value in B2. Otherwise, display nothing."

You can copy and paste this formula down the entire column to apply it to all rows. Let me know if you have any questions!

-------------------------------------------------------------------------

Dear Iyanna, Good Afternoon.

Your explanation was very well.

This a typical case to use the VLOOKUP FUNCTION.

Your Scenery:

___A____________B__________C__________D_E_F
1__Account Num__$ Amount___$ Amount 2_________Account to be Excluded
2_______1__________10______=FORMULA__________2
3_______2__________33______=FORMULA__________4
4_______3___________5______=FORMULA__________5
5_______4__________89______=FORMULA__________8
6_______5_________150______=FORMULA
7_______6__________16______=FORMULA
8_______7_________865______=FORMULA
9_______8__________67______=FORMULA
10______9__________44______=FORMULA


FORMULA:
C2 -- =IF(ISERROR(VLOOKUP(A2,$F$2:$F$10,1,FALSE)),B2,"DE LETE")

Copy it down untill C10


The results must be like these ones:

___A____________B__________C__________D_E_F
1__Account Num__$ Amount___$ Amount 2_________Account to be Excluded
2_______1__________10_________10_______________2
3_______2__________33________DELETE____________4
4_______3___________5__________5_______________5
5_______4__________89________DELETE____________8
6_______5_________150________DELETE
7_______6__________16_________16
8_______7_________865________DELETE
9_______8__________67_________67
10______9__________44_________44

You must adapt the contents of column C, eg. BLANK
You must adapt the interval of column F as your reality.
In this case the search works at F2:F10 but your necessity can be F2:F1450

Implement this one and tell me if it worked for you.

Fell free to ask anything about it.

Thank you for your reply. But this formula is still resulting in an amount being shown in Column C, even when that account number is present on the list of accounts to exclude.

-------------------------------------------------------------------------

Dear Iyanna, Good Afternoon.

"...But this formula is still resulting in an amount being shown in Column C, even when that account number is present on the list of accounts to exclude..."

It´s sounds so strange.

Are you sure that you typed correctly the formulas?

The EXACT example is saved here : http://www.4shared.com/document/A6YQ...r_VLOOKUP.html

Please, take a look at it.

If you need help to implement this formula to your worksheet, save it at a free site, www.4shared.com, and put the link here.

I can and I want help you to solve this problem.

I think there was a problem with the way the account numbers were formatted in Column A. I haven't quite figured it out yet, but if I type them over, the formula works. THANK YOU SO MUCH!! I've been working on this for two days!!

------------------------------------------------------------------------

Dear Iyanna.

I feel glad to help you.
You´re Welcome.

Have a nice day.

Using code:
If Not Application.IntersectRangerng_trigger, Target Is Nothing Then

it sees it as Nothing and skips to Else:

To be clear, Rangerng_trigger is not the active cell,
another change in the worksheet causes Rangerng_trigger to change value,
only when the value changes in the named range should it trigger macro to run.