Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default 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?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: IF this value IS NOT in this range THEN

Formula for Column C:

Formula:
=IF(ISERROR(MATCH(A2,$F$2:$F$21,0)),B2,""
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!
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Senior Member
 
Location: Belo Horizonte, Brazil
Posts: 170
Thumbs up

Quote:
Originally Posted by Iyanna View Post
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?
-------------------------------------------------------------------------

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.
__________________
I hope it can help you.

Best regards,
Marcilio Lobão
---------------------------
Belo Horizonte, Brazil
  #4   Report Post  
Junior Member
 
Posts: 3
Default

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.

Quote:
Originally Posted by Mazzaropi View Post
-------------------------------------------------------------------------

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.
  #5   Report Post  
Senior Member
 
Location: Belo Horizonte, Brazil
Posts: 170
Thumbs up

Quote:
Originally Posted by Iyanna View Post
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 hope it can help you.

Best regards,
Marcilio Lobão
---------------------------
Belo Horizonte, Brazil


  #6   Report Post  
Junior Member
 
Posts: 3
Default

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!!
  #7   Report Post  
Senior Member
 
Location: Belo Horizonte, Brazil
Posts: 170
Thumbs up

Quote:
Originally Posted by Iyanna View Post
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.
__________________
I hope it can help you.

Best regards,
Marcilio Lobão
---------------------------
Belo Horizonte, Brazil
  #8   Report Post  
Banned
 
Location: UK
Posts: 2
Default

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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I enter formula sum(range+range)*0.15 sumif(range=3) tkw Excel Discussion (Misc queries) 2 October 1st 09 09:17 PM
Cond. Format Data Bars of range based on values of another range alexmo Excel Worksheet Functions 4 January 16th 09 05:03 AM
Excel Addin:Setting the range to the Excel.Range object range prop Rp007 Excel Worksheet Functions 5 November 24th 06 05:30 PM
Selecting range in list of range names depending on a cell informa Courreges Excel Discussion (Misc queries) 2 June 19th 06 10:59 AM
How to count dates within a certain range in a column with mutiple date range entries Krisjhn Excel Worksheet Functions 2 September 1st 05 01:59 PM


All times are GMT +1. The time now is 09:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"