concise if statements in Excel 2013 - how?

PKBrahma

New Member
Joined
Nov 26, 2017
Messages
28
Sir,
I long posted a question and found answer in (/forum/excel-questions/1036441-match-nested-if-vlookup-2.html). I have gone to the next step and wrote this (if)conditional code by myself which is very long. How can I shorten it? I am getting data to sheet3 from paymatrix sheet in the same workbook. If possible, please help me shortening the code. Thanks. Code is given below:

IF(C6=1900,INDEX(paymatrix!$A$3:$A$19,IFNA(MATCH(D6,paymatrix!$A$3:$A$19,0),MATCH(D6,paymatrix!$A$3:$A$19,1)+1)),
IF(C6=4200,INDEX(paymatrix!$B$3:$B$19,IFNA(MATCH(D6,paymatrix!$B$3:$B$19,0),MATCH(D6,paymatrix!$B$3:$B$19,1)+1)),
IF(C6=4600,INDEX(paymatrix!$C$3:$C$19,IFNA(MATCH(D6,paymatrix!$C$3:$C$19,0),MATCH(D6,paymatrix!$C$3:$C$19,1)+1)),
IF(C6=5400,INDEX(paymatrix!$D$3:$D$19,IFNA(MATCH(D6,paymatrix!$D$3:$D$19,0),MATCH(D6,paymatrix!$D$3:$D$19,1)+1)),
IF(C6=6600,INDEX(paymatrix!$E$3:$E$19,IFNA(MATCH(D6,paymatrix!$E$3:$E$19,0),MATCH(D6,paymatrix!$E$3:$E$19,1)+1)))))))
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

dave2018

Board Regular
Joined
Mar 20, 2018
Messages
223
I haven't checked your old post on the link u gave, but here's for starters:

=INDEX(INDEX(paymatrix!$A$3:$E$19,,VLOOKUP(C6,{1900,1;4200,2;4600,3;5400,4;6600,5},2,0)),IFNA(MATCH(D6,INDEX(paymatrix!$A$3:$E$19,,VLOOKUP(C6,{1900,1;4200,2;4600,3;5400,4;6600,5},2,0)),0),MATCH(D6,INDEX(paymatrix!$A$3:$E$19,,VLOOKUP(C6,{1900,1;4200,2;4600,3;5400,4;6600,5},2,0)),1)+1))

(it takes away all the ifs, so calculation time is much faster.) Does that help?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,280
Office Version
  1. 365
Platform
  1. Windows
Another possibility to try

=INDEX(paymatrix!$A$3:$E$19,MATCH(D6,INDEX(paymatrix!$A$3:$E$19,0,MATCH(C6,{1900,4200,4600,5400,6600},0)),1)
+(COUNTIF(INDEX(paymatrix!$A$3:$E$19,0,MATCH(C6,{1900,4200,4600,5400,6600},0)),D6)=0),MATCH(C6,{1900,4200,4600,5400,6600},0))

However, the repetition in the formula leads me to suggest the possibility of a helper column. For example, in
Z6 (if that column is available)
=MATCH(C6,{1900,4200,4600,5400,6600},0)

Then the main formula shortens to just
=INDEX(paymatrix!$A$3:$E$19,MATCH(D6,INDEX(paymatrix!$A$3:$E$19,0,Z6),1)+(COUNTIF(INDEX(paymatrix!$A$3:$E$19,0,Z6),D6)=0),Z6)
 
Last edited:

PKBrahma

New Member
Joined
Nov 26, 2017
Messages
28
I haven't checked your old post on the link u gave, but here's for starters:

=INDEX(INDEX(paymatrix!$A$3:$E$19,,VLOOKUP(C6,{1900,1;4200,2;4600,3;5400,4;6600,5},2,0)),IFNA(MATCH(D6,INDEX(paymatrix!$A$3:$E$19,,VLOOKUP(C6,{1900,1;4200,2;4600,3;5400,4;6600,5},2,0)),0),MATCH(D6,INDEX(paymatrix!$A$3:$E$19,,VLOOKUP(C6,{1900,1;4200,2;4600,3;5400,4;6600,5},2,0)),1)+1))

(it takes away all the ifs, so calculation time is much faster.) Does that help?


Sir,
It's been fantastic. Even, the one shown below yours is much shorter. Thank you very much.
 

PKBrahma

New Member
Joined
Nov 26, 2017
Messages
28

ADVERTISEMENT

Another possibility to try

=INDEX(paymatrix!$A$3:$E$19,MATCH(D6,INDEX(paymatrix!$A$3:$E$19,0,MATCH(C6,{1900,4200,4600,5400,6600},0)),1)
+(COUNTIF(INDEX(paymatrix!$A$3:$E$19,0,MATCH(C6,{1900,4200,4600,5400,6600},0)),D6)=0),MATCH(C6,{1900,4200,4600,5400,6600},0))

However, the repetition in the formula leads me to suggest the possibility of a helper column. For example, in
Z6 (if that column is available)
=MATCH(C6,{1900,4200,4600,5400,6600},0)

Then the main formula shortens to just
=INDEX(paymatrix!$A$3:$E$19,MATCH(D6,INDEX(paymatrix!$A$3:$E$19,0,Z6),1)+(COUNTIF(INDEX(paymatrix!$A$3:$E$19,0,Z6),D6)=0),Z6)

Sir,
Just fantastic. The shortest ever code. Thanks & regards.
 

PKBrahma

New Member
Joined
Nov 26, 2017
Messages
28
Sir,
Just fantastic. The shortest ever code. Thanks & regards.

Sir,
Actually what do you do if there is an increment in the if condition? I mean if I add 2 at end of each if condition.
IF(C6=1900,INDEX(paymatrix!$A$3:$A$19,IFNA(MATCH(D6,paymatrix!$A$3:$A$19,0),MATCH(D6,paymatrix!$A$3:$A$19,1)+2)),
IF(C6=4200,INDEX(paymatrix!$B$3:$B$19,IFNA(MATCH(D6,paymatrix!$B$3:$B$19,0),MATCH(D6,paymatrix!$B$3:$B$19,1)+2)),
IF(C6=4600,INDEX(paymatrix!$C$3:$C$19,IFNA(MATCH(D6,paymatrix!$C$3:$C$19,0),MATCH(D6,paymatrix!$C$3:$C$19,1)+2)),
IF(C6=5400,INDEX(paymatrix!$D$3:$D$19,IFNA(MATCH(D6,paymatrix!$D$3:$D$19,0),MATCH(D6,paymatrix!$D$3:$D$19,1)+2)),
IF(C6=6600,INDEX(paymatrix!$E$3:$E$19,IFNA(MATCH(D6,paymatrix!$E$3:$E$19,0),MATCH(D6,paymatrix!$E$3:$E$19,1)+2)))))))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,280
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sir,
Actually what do you do if there is an increment in the if condition? I mean if I add 2 at end of each if condition.
For each of my suggestions, you would add in the blue text as shown just before the COUNTIF

... +2*(COUNTIF(INDEX( ....
 

dave2018

Board Regular
Joined
Mar 20, 2018
Messages
223
Another possibility to try

=INDEX(paymatrix!$A$3:$E$19,MATCH(D6,INDEX(paymatrix!$A$3:$E$19,0,MATCH(C6,{1900,4200,4600,5400,6600},0)),1)
+(COUNTIF(INDEX(paymatrix!$A$3:$E$19,0,MATCH(C6,{1900,4200,4600,5400,6600},0)),D6)=0),MATCH(C6,{1900,4200,4600,5400,6600},0))

However, the repetition in the formula leads me to suggest the possibility of a helper column. For example, in
Z6 (if that column is available)
=MATCH(C6,{1900,4200,4600,5400,6600},0)

Then the main formula shortens to just
=INDEX(paymatrix!$A$3:$E$19,MATCH(D6,INDEX(paymatrix!$A$3:$E$19,0,Z6),1)+(COUNTIF(INDEX(paymatrix!$A$3:$E$19,0,Z6),D6)=0),Z6)

You're right with replacing my vlookup to a match. didn't think of that
 

PKBrahma

New Member
Joined
Nov 26, 2017
Messages
28
For each of my suggestions, you would add in the blue text as shown just before the COUNTIF

... +2*(COUNTIF(INDEX( ....

Sir,
Regards. Worked beautifully. I owe you so much for the code. And more for your quick response.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,313
Messages
5,527,952
Members
409,794
Latest member
ajithppajith

This Week's Hot Topics

Top