|
MATCH not working in IF statement
I have a IF formula that uses the MATCH function for the SAME search column
for all areas of the IF formula - that is the LOGICAL TEST, TRUE, & FALSE statements. Each IF sections (each formula for Logical Test, True, and False statements) works correctly by themselves in a single cell, but when combined in the IF statement there is a problem. The both Logical Test and True statements MATCH lookup value is 2, and the False statement formula then MATCH lookup value is 1. This IF formula fails to MATCH lookup the value of 1, that is when the value is 1 the IF statement should then only run the FALSE formula because it matches 1, but for some reason in this case the result it gives #N/A. (I'm thinking it reads the LOGICAL TEST & TRUE result of False as #N/A, and fails to activate the FALSE formual?) How can this be fixed? Thank you. -- John 3:16-19 |
MATCH not working in IF statement
Here is the actual formula:
=IF(INDEX(C257:C1624,MATCH(2,AB257:AB1598,0))=INDE X(C257:C1624,MATCH(2,AB257:AB1598,0)),INDEX(C257:C 1624,MATCH(2,AB257:AB1598,0)),(INDEX(AA257:AA1624* $B$12,MATCH(1,AB257:AB1598,0)))) -- John 3:16-19 "yogart" wrote: I have a IF formula that uses the MATCH function for the SAME search column for all areas of the IF formula - that is the LOGICAL TEST, TRUE, & FALSE statements. Each IF sections (each formula for Logical Test, True, and False statements) works correctly by themselves in a single cell, but when combined in the IF statement there is a problem. The both Logical Test and True statements MATCH lookup value is 2, and the False statement formula then MATCH lookup value is 1. This IF formula fails to MATCH lookup the value of 1, that is when the value is 1 the IF statement should then only run the FALSE formula because it matches 1, but for some reason in this case the result it gives #N/A. (I'm thinking it reads the LOGICAL TEST & TRUE result of False as #N/A, and fails to activate the FALSE formual?) How can this be fixed? Thank you. -- John 3:16-19 |
MATCH not working in IF statement
Can't tell what you're wanting to do but....
Your logical test will *always* be TRUE (unless MATCH errors) so you will *never* get to the value_if_false argument. Here's the logic your formula is evaluating: IF: INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) equals INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) return INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) else return INDEX(AA257:AA1624*$B$12,MATCH(1,AB257:AB1598,0)) If this value equals itself, return the value, else return this value. The problem is, the value will *always* equal itself (unless MATCH errors). -- Biff Microsoft Excel MVP "yogart" . wrote in message ... I have a IF formula that uses the MATCH function for the SAME search column for all areas of the IF formula - that is the LOGICAL TEST, TRUE, & FALSE statements. Each IF sections (each formula for Logical Test, True, and False statements) works correctly by themselves in a single cell, but when combined in the IF statement there is a problem. The both Logical Test and True statements MATCH lookup value is 2, and the False statement formula then MATCH lookup value is 1. This IF formula fails to MATCH lookup the value of 1, that is when the value is 1 the IF statement should then only run the FALSE formula because it matches 1, but for some reason in this case the result it gives #N/A. (I'm thinking it reads the LOGICAL TEST & TRUE result of False as #N/A, and fails to activate the FALSE formual?) How can this be fixed? Thank you. -- John 3:16-19 |
MATCH not working in IF statement
If it does not Match a lookup value of 2 (there is not 2 in the look up
column) which would make it false, then should it run the False statement and Match for lookup value 1 and work if there is a 1 in the lookup column? This IF formula works fine when finding a 2 in the lookup column, but it there is only a 1 in the lookup column it will not run the false statement. -- John 3:16-19 "T. Valko" wrote: Can't tell what you're wanting to do but.... Your logical test will *always* be TRUE (unless MATCH errors) so you will *never* get to the value_if_false argument. Here's the logic your formula is evaluating: IF: INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) equals INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) return INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) else return INDEX(AA257:AA1624*$B$12,MATCH(1,AB257:AB1598,0)) If this value equals itself, return the value, else return this value. The problem is, the value will *always* equal itself (unless MATCH errors). -- Biff Microsoft Excel MVP "yogart" . wrote in message ... I have a IF formula that uses the MATCH function for the SAME search column for all areas of the IF formula - that is the LOGICAL TEST, TRUE, & FALSE statements. Each IF sections (each formula for Logical Test, True, and False statements) works correctly by themselves in a single cell, but when combined in the IF statement there is a problem. The both Logical Test and True statements MATCH lookup value is 2, and the False statement formula then MATCH lookup value is 1. This IF formula fails to MATCH lookup the value of 1, that is when the value is 1 the IF statement should then only run the FALSE formula because it matches 1, but for some reason in this case the result it gives #N/A. (I'm thinking it reads the LOGICAL TEST & TRUE result of False as #N/A, and fails to activate the FALSE formual?) How can this be fixed? Thank you. -- John 3:16-19 |
MATCH not working in IF statement
If the MATCH errors (returns #N/A) that's where the formula stops and that
is the result of the formula. It *never* gets to the value_if_false argument: =IF(#N/A=#N/A,#N/A) The logic of your formula doesn't make any sense. You're looking up a value: INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) And you're comparing that value to itself: IF: INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) equals INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) Like I noted, this will *always* be true unless MATCH errors and when MATCH errors that's where the formula stops. You would need to incorporate an error check so then it can get to the value_if_false argument. This is my best guess at what you're trying to do: =IF(COUNT(MATCH(2,AB257:AB1598,0)),INDEX(C257:C159 8,MATCH(2,AB257:AB1598,0)),INDEX(AA257:AA1598,MATC H(1,AB257:AB1598,0))*$B$12) -- Biff Microsoft Excel MVP "yogart" . wrote in message ... If it does not Match a lookup value of 2 (there is not 2 in the look up column) which would make it false, then should it run the False statement and Match for lookup value 1 and work if there is a 1 in the lookup column? This IF formula works fine when finding a 2 in the lookup column, but it there is only a 1 in the lookup column it will not run the false statement. -- John 3:16-19 "T. Valko" wrote: Can't tell what you're wanting to do but.... Your logical test will *always* be TRUE (unless MATCH errors) so you will *never* get to the value_if_false argument. Here's the logic your formula is evaluating: IF: INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) equals INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) return INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) else return INDEX(AA257:AA1624*$B$12,MATCH(1,AB257:AB1598,0)) If this value equals itself, return the value, else return this value. The problem is, the value will *always* equal itself (unless MATCH errors). -- Biff Microsoft Excel MVP "yogart" . wrote in message ... I have a IF formula that uses the MATCH function for the SAME search column for all areas of the IF formula - that is the LOGICAL TEST, TRUE, & FALSE statements. Each IF sections (each formula for Logical Test, True, and False statements) works correctly by themselves in a single cell, but when combined in the IF statement there is a problem. The both Logical Test and True statements MATCH lookup value is 2, and the False statement formula then MATCH lookup value is 1. This IF formula fails to MATCH lookup the value of 1, that is when the value is 1 the IF statement should then only run the FALSE formula because it matches 1, but for some reason in this case the result it gives #N/A. (I'm thinking it reads the LOGICAL TEST & TRUE result of False as #N/A, and fails to activate the FALSE formual?) How can this be fixed? Thank you. -- John 3:16-19 |
MATCH not working in IF statement
Yes, your guess correction works perfectly. You saved me alot of time and
I'll learn from it. Thank you for your help. : ) -- John 3:16-19 "T. Valko" wrote: If the MATCH errors (returns #N/A) that's where the formula stops and that is the result of the formula. It *never* gets to the value_if_false argument: =IF(#N/A=#N/A,#N/A) The logic of your formula doesn't make any sense. You're looking up a value: INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) And you're comparing that value to itself: IF: INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) equals INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) Like I noted, this will *always* be true unless MATCH errors and when MATCH errors that's where the formula stops. You would need to incorporate an error check so then it can get to the value_if_false argument. This is my best guess at what you're trying to do: =IF(COUNT(MATCH(2,AB257:AB1598,0)),INDEX(C257:C159 8,MATCH(2,AB257:AB1598,0)),INDEX(AA257:AA1598,MATC H(1,AB257:AB1598,0))*$B$12) -- Biff Microsoft Excel MVP "yogart" . wrote in message ... If it does not Match a lookup value of 2 (there is not 2 in the look up column) which would make it false, then should it run the False statement and Match for lookup value 1 and work if there is a 1 in the lookup column? This IF formula works fine when finding a 2 in the lookup column, but it there is only a 1 in the lookup column it will not run the false statement. -- John 3:16-19 "T. Valko" wrote: Can't tell what you're wanting to do but.... Your logical test will *always* be TRUE (unless MATCH errors) so you will *never* get to the value_if_false argument. Here's the logic your formula is evaluating: IF: INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) equals INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) return INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) else return INDEX(AA257:AA1624*$B$12,MATCH(1,AB257:AB1598,0)) If this value equals itself, return the value, else return this value. The problem is, the value will *always* equal itself (unless MATCH errors). -- Biff Microsoft Excel MVP "yogart" . wrote in message ... I have a IF formula that uses the MATCH function for the SAME search column for all areas of the IF formula - that is the LOGICAL TEST, TRUE, & FALSE statements. Each IF sections (each formula for Logical Test, True, and False statements) works correctly by themselves in a single cell, but when combined in the IF statement there is a problem. The both Logical Test and True statements MATCH lookup value is 2, and the False statement formula then MATCH lookup value is 1. This IF formula fails to MATCH lookup the value of 1, that is when the value is 1 the IF statement should then only run the FALSE formula because it matches 1, but for some reason in this case the result it gives #N/A. (I'm thinking it reads the LOGICAL TEST & TRUE result of False as #N/A, and fails to activate the FALSE formual?) How can this be fixed? Thank you. -- John 3:16-19 |
MATCH not working in IF statement
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "yogart" . wrote in message ... Yes, your guess correction works perfectly. You saved me alot of time and I'll learn from it. Thank you for your help. : ) -- John 3:16-19 "T. Valko" wrote: If the MATCH errors (returns #N/A) that's where the formula stops and that is the result of the formula. It *never* gets to the value_if_false argument: =IF(#N/A=#N/A,#N/A) The logic of your formula doesn't make any sense. You're looking up a value: INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) And you're comparing that value to itself: IF: INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) equals INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) Like I noted, this will *always* be true unless MATCH errors and when MATCH errors that's where the formula stops. You would need to incorporate an error check so then it can get to the value_if_false argument. This is my best guess at what you're trying to do: =IF(COUNT(MATCH(2,AB257:AB1598,0)),INDEX(C257:C159 8,MATCH(2,AB257:AB1598,0)),INDEX(AA257:AA1598,MATC H(1,AB257:AB1598,0))*$B$12) -- Biff Microsoft Excel MVP "yogart" . wrote in message ... If it does not Match a lookup value of 2 (there is not 2 in the look up column) which would make it false, then should it run the False statement and Match for lookup value 1 and work if there is a 1 in the lookup column? This IF formula works fine when finding a 2 in the lookup column, but it there is only a 1 in the lookup column it will not run the false statement. -- John 3:16-19 "T. Valko" wrote: Can't tell what you're wanting to do but.... Your logical test will *always* be TRUE (unless MATCH errors) so you will *never* get to the value_if_false argument. Here's the logic your formula is evaluating: IF: INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) equals INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) return INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)) else return INDEX(AA257:AA1624*$B$12,MATCH(1,AB257:AB1598,0)) If this value equals itself, return the value, else return this value. The problem is, the value will *always* equal itself (unless MATCH errors). -- Biff Microsoft Excel MVP "yogart" . wrote in message ... I have a IF formula that uses the MATCH function for the SAME search column for all areas of the IF formula - that is the LOGICAL TEST, TRUE, & FALSE statements. Each IF sections (each formula for Logical Test, True, and False statements) works correctly by themselves in a single cell, but when combined in the IF statement there is a problem. The both Logical Test and True statements MATCH lookup value is 2, and the False statement formula then MATCH lookup value is 1. This IF formula fails to MATCH lookup the value of 1, that is when the value is 1 the IF statement should then only run the FALSE formula because it matches 1, but for some reason in this case the result it gives #N/A. (I'm thinking it reads the LOGICAL TEST & TRUE result of False as #N/A, and fails to activate the FALSE formual?) How can this be fixed? Thank you. -- John 3:16-19 |
| All times are GMT +1. The time now is 04:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com