IF THEN with Match & Index formula not working

[yogart]

=IF(INDEX(C570:C1911,MATCH(2,P5701911)>=(I6*$B$2)),3,0)

In the above formula in the true ‘3’ works for the logical test, but the
false ‘0’ will not trigger - it keeps showing ‘3’ if false.

Example, if the pulled Index value is 100, and I6*$B$2 = 200, then it
should trigger 0 for false, but it shows 3 true.

Any recommendations please?

Thank you.

 

[Pete_UK]

I think you have a bracket in the wrong place - try this:

=IF(INDEX(C570:C1911,MATCH(2,P5701911))>=(I6*$B$2),3,0)

You don't really need brackets around I6*$B$2 either.

Hope this helps.

Pete

 

[yogart]

Still not fixed. The new formula triggers the false statement 0, but now
fails to triggers the true statement 3 when the appropriate Index pulled
number. Any suggestion would be appreciated. : )

 

[Shane Devenshire]

Hi,

There are a lot of issues with this formula:
=IF(INDEX(C570:C1911,MATCH(2,P5701911))>=(I6*$B$2),3,0)

First simplify it to

=IF(INDEX(C570:C1911,MATCH(2,P5701911)>=I6*$B$2),3,0)

Second consider this portion MATCH(2,P5701911)>=I6*$B$2

It will return either TRUE or FALSE. In Excel FALSE is equivalent to 0 and
TRUE to 1, so in all cases your INDEX will be at 1,0 or 0,0. Why you are
doing an index at all is unclear. This means that the index function return
the item at cell C570 if the match is found. In fact the INDEX(X,1) and
INDEX(X,0) return the first item of the range.

We might be able to help you more if we knew what you were trying to do

 

[T. Valko]

=IF(INDEX(C570:C1911,MATCH(2,P5701911))>=(I6*$B$2),3,0)

Are you sure MATCH is finding the correct value?

Try this and see if it returns the correct value:

=INDEX(C570:C1911,MATCH(2,P5701911))

You really don't need the IF, you can write the formula like this:

=(INDEX(C570:C1911,MATCH(2,P5701911))>=I6*$B$2)*3

 

[T. Valko]

There are a lot of issues with this formula:

=IF(INDEX(C570:C1911,MATCH(2,P5701911))>=(I6*$B$2),3,0)

That formula is syntactically correct. You can remove the superfluous set of
( ) around I6*B2 but that's hardly an "issue".

First simplify it to
=IF(INDEX(C570:C1911,MATCH(2,P5701911)>=I6*$B$2),3,0)

OK, *now* the formula has issues!

Second consider this portion MATCH(2,P5701911)>=I6*$B$2

I'm pretty sure this is what they're trying to compare:

INDEX(C570:C1911,MATCH(2,P5701911))

=

I6*B2

I suspect the formula doesn't work correctly due to the way they're using
MATCH.

Awaiting a response from the OP.

 

[yogart]

You said:
Try this and see if it returns the correct value:

=INDEX(C570:C1911,MATCH(2,P5701911))

It's result is 0. It should have grabbed the value in cell C573 which is 10.

Your formuala:
=(INDEX(C570:C1911,MATCH(2,P5701911))>=I6*$B$2)*3

shows 0 when the value in C573 is 10, but if I change C573 to 100, then it
still shows 0.


What I’m trying to do is Match 2, which first hits in P537. Then I want to
Index that value in the same row which is C573. If C573 >= I6*$B$2, 3, 0.
Note: I6*$B$2 = 56.56.

Thanks for your time and effort. : )

 

[T. Valko]

What I'm trying to do is Match 2, which first hits in P537.

Try it like this:

=(INDEX(C570:C1911,MATCH(2,P5701911,0))>=I6*$B$2)*3

The 0 argument added to the MATCH function means you want an exact match.


--
Biff
Microsoft Excel MVP

You said:
Try this and see if it returns the correct value:

=INDEX(C570:C1911,MATCH(2,P5701911))

It's result is 0. It should have grabbed the value in cell C573 which is
10.

Your formuala:
=(INDEX(C570:C1911,MATCH(2,P5701911))>=I6*$B$2)*3

shows 0 when the value in C573 is 10, but if I change C573 to 100, then it
still shows 0.


What I'm trying to do is Match 2, which first hits in P537. Then I want
to
Index that value in the same row which is C573. If C573 >= I6*$B$2, 3,
0.
Note: I6*$B$2 = 56.56.

Thanks for your time and effort. : )

 

[yogart]

Hello T. Valko. This is the second time that you helped me big time. I
wasted several hours trying to fix this with no sucess. Your help is very
much appreciated. Thank you also to Shane.

May God bless your weekend. : )

 

[T. Valko]

You're welcome. Thanks for the feedback!

I wasted several hours trying to fix this with no sucess.

Don't wait so long to pay us a visit next time!

 

[T. Valko]

Thank you also to Shane.

Hey, let's give Pete a shoutout! He was on the right track too.

 

[yogart]

Thank you Pete : ) : ) : )

 

[Pete_UK]

You're welcome - I've been in the land of nod while you've been
discussing this.

Pete

Thank you Pete : )       : )          : )

--
John 3:16-19

Hey, let's give Pete a shoutout! He was on the right track too.

 

[yogart]

I have been struggling and need some more help with this formula. I would
like to add to it. Here is that formula that we were working with:

=(INDEX(C570:C1911,MATCH(2,P5701911,0))>=I6*$B$2)*3

I would like to add an IF statement to the above formula saying :
IF =(INDEX(C570:C1911,MATCH(2,P5701911,0))>=I6*$B$2)*3 Equals 3,
then =INDEX(C570:C1911,MATCH(2,P5701911,0)) , if false then I6*$B$2

Any help would be appreciated.

Thank you.

--
John 3:16-19

You're welcome - I've been in the land of nod while you've been
discussing this.

Pete

 

[T. Valko]

Try this:

=IF(INDEX(C570:C1911,MATCH(2,P5701911,0))>=I6*$B$2,INDEX(C570:C1911,MATCH(2,P5701911,0)),I6*$B$2)


--
Biff
Microsoft Excel MVP

I have been struggling and need some more help with this formula. I would
like to add to it. Here is that formula that we were working with:

=(INDEX(C570:C1911,MATCH(2,P5701911,0))>=I6*$B$2)*3

I would like to add an IF statement to the above formula saying :
IF =(INDEX(C570:C1911,MATCH(2,P5701911,0))>=I6*$B$2)*3 Equals
3,
then =INDEX(C570:C1911,MATCH(2,P5701911,0)) , if false then I6*$B$2

Any help would be appreciated.

Thank you.

 

[yogart]

Thank you Biff for your time and effort! Very much appreciated.

1) Do you work for Microsoft?
2) Do you sleep? It seems you are alway there with fast response.

Thank and may God bless your day. : )

 

[T. Valko]

1. no
2. sometimes!

You're welcome!