How to stop a formula when the IF statement is true

S

sb1989

New Member
Joined
Jul 30, 2014
Messages
7
Hello,

I am working on a formula that has many nested IF statements. I am calculating distances from stores, where the distances are in a matrix. Here is a short version of the formula to get from store to store.

=INDEX(C3:H8,MATCH(A13,A3:A8,0),MATCH(B13,C1:H1,0))+IF(ISBLANK(B14),(INDEX(C3:H8,MATCH(B13,A3:A8,0),MATCH(C13,C1:H1,0))),INDEX(C3:H8,MATCH(B13,A3:A8,0),MATCH(B14,C1:H1,0)))+IF(ISBLANK(B15),(INDEX(C3:H8,MATCH(B14,A3:A8,0),MATCH(C14,C1:H1,0))),INDEX(C3:H8,MATCH(B14,A3:A8,0),MATCH(B15,C1:H1,0)))+IF(ISBLANK(B16),(INDEX(C3:H8,MATCH(B15,A3:A8,0),MATCH(C15,C1:H1,0))),INDEX(C3:H8,MATCH(B15,A3:A8,0),MATCH(B16,C1:H1,0)))

The index is the matrix. The problem I am having is that I want the formula to stop calculating and ignore the remaining parts after the IF statement is true (true statement = cell below is empty). I am currently getting an error when one of the stores is missing and is not the final store in the formula.


I have posted the matrix and store layout below. Please let me know if this possible, or if there is a better way to complete this!

Much apprecaited!
Sam
[TABLE="width: 384"]
<TBODY>[TR]
[TD="class: xl159, width: 64, bgcolor: #dbe5f1"]Store #
[/TD]
[TD="class: xl159, width: 64, bgcolor: #dbe5f1"][/TD]
[TD="class: xl159, width: 64, bgcolor: #dbe5f1, align: right"]2002
[/TD]
[TD="class: xl159, width: 64, bgcolor: #dbe5f1, align: right"]2004
[/TD]
[TD="class: xl159, width: 64, bgcolor: #dbe5f1, align: right"]2005
[/TD]
[TD="class: xl159, width: 64, bgcolor: #dbe5f1, align: right"]2006
[/TD]
[TD="class: xl159, width: 64, bgcolor: #dbe5f1, align: right"]2007
[/TD]
[TD="class: xl159, width: 64, bgcolor: #dbe5f1, align: right"]2008
[/TD]
[/TR]
[TR]
[TD="class: xl159, bgcolor: #dbe5f1"][/TD]
[TD="class: xl160, bgcolor: #b8cce4"]Postal Code
[/TD]
[TD="class: xl160, bgcolor: #b8cce4"]L6V 1B7
[/TD]
[TD="class: xl160, bgcolor: #b8cce4"]1747 countryside
[/TD]
[TD="class: xl160, bgcolor: #b8cce4"]L6Z 1Y4
[/TD]
[TD="class: xl160, bgcolor: #b8cce4"]L6T 4G8
[/TD]
[TD="class: xl160, bgcolor: #b8cce4"]L7E 4Z8
[/TD]
[TD="class: xl160, bgcolor: #b8cce4"]L7G 4B1
[/TD]
[/TR]
[TR]
[TD="class: xl159, bgcolor: #dbe5f1, align: right"]2002
[/TD]
[TD="class: xl160, bgcolor: #b8cce4"]L6V 1B7
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]0.00
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]12.40
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]7.56
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]6.74
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]25.40
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]18.35
[/TD]
[/TR]
[TR]
[TD="class: xl159, bgcolor: #dbe5f1, align: right"]2004
[/TD]
[TD="class: xl160, bgcolor: #b8cce4"]1747 countryside
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]12.40
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]0.00
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]6.06
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]9.45
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]16.60
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]20.23
[/TD]
[/TR]
[TR]
[TD="class: xl159, bgcolor: #dbe5f1, align: right"]2005
[/TD]
[TD="class: xl160, bgcolor: #b8cce4"]L6Z 1Y4
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]8.08
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]6.60
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]0.00
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]10.76
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]21.33
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]16.34
[/TD]
[/TR]
[TR]
[TD="class: xl159, bgcolor: #dbe5f1, align: right"]2006
[/TD]
[TD="class: xl160, bgcolor: #b8cce4"]L6T 4G8
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]6.75
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]9.45
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]10.21
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]0.00
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]23.72
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]22.19
[/TD]
[/TR]
[TR]
[TD="class: xl159, bgcolor: #dbe5f1, align: right"]2007
[/TD]
[TD="class: xl160, bgcolor: #b8cce4"]L7E 4Z8
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]25.39
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]16.59
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]20.76
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]23.23
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]0.00
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]32.23
[/TD]
[/TR]
[TR]
[TD="class: xl159, bgcolor: #dbe5f1, align: right"]2008
[/TD]
[TD="class: xl160, bgcolor: #b8cce4"]L7G 4B1
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]18.36
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]20.23
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]16.53
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]22.16
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]32.26
[/TD]
[TD="class: xl161, bgcolor: transparent, align: right"]0.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl164, bgcolor: #dbe5f1"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Start
[/TD]
[TD="bgcolor: transparent"]Store
[/TD]
[TD="bgcolor: transparent"]End
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2002
[/TD]
[TD="bgcolor: transparent, align: right"]2004
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl165, bgcolor: yellow, align: right"]52.93
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl162, bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2005
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl162, bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2006
[/TD]
[TD="bgcolor: transparent, align: right"]2007
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl162, bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl163, bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]**the highlighted cell contains the formula above.
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
 
Sort by date Sort by votes
sb1989,
Have you considered to split the formula? Maybe you can write the Start # (2002) in cell B13, and the End# (2007) in cell B17 then the formula in row C14 would be =IF(B14<>"",INDEX($C$3:$H$8,MATCH(B13,$A$3:$A$8,0),MATCH(B14,$C$1:$H$1,0)),"") and you can copy this formula down covering as much cells as you want. The cell C12 can have formula =SUM(C13:C19) to add all individual distances. The good thing is that you can expand for as many rows as you need; just copy the formula to calculate the distance and make sure the addition =SUM(C13:C19) includes the new rows.
I hope this helps,
GFV
 
Upvote 0
You must log in or register to reply here.

Similar threads

mehidy1437
  • Question Question
Correction needed in formula INDEX AGGREGATE
Replies
3
Views
800
Fluff
Fluff
C
  • Question Question
need some advice/help: pivot table, matrix, index/match?
Replies
0
Views
871
cmr72
C
Joneye
  • Question Question
Index & Match Question
Replies
4
Views
637
Joneye
Joneye
B
  • Question Question
HELP on INDEX/MATCH multiple ocurence match values needed ..DRIVING ME CRAZY!!!
2
Replies
11
Views
5K
Aladin Akyurek
Aladin Akyurek

Forum statistics

Threads
1,226,840
Messages
6,193,279
Members
453,788
Latest member
drcharle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top