MATCH not working in IF statement

[yogart]

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.

 

[yogart]

Here is the actual formula
=IF(INDEX(C257:C1624,MATCH(2,AB257:AB1598,0))=INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)),INDEX(C257:C1624,MATCH(2,AB257:AB1598,0)),(INDEX(AA257:AA1624*$B$12,MATCH(1,AB257:AB1598,0))))

 

[T. Valko]

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).

 

[yogart]

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.

 

[T. Valko]

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:C1598,MATCH(2,AB257:AB1598,0)),INDEX(AA257:AA1598,MATCH(1,AB257:AB1598,0))*$B$12)

 

[yogart]

Yes, your guess correction works perfectly. You saved me alot of time and
I'll learn from it. Thank you for your help. : )

 

[T. Valko]

You're welcome. Thanks for the feedback!