concise if statements in Excel 2013 - how?

[PKBrahma]

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

 

[dave2018]

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]

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)

 

[PKBrahma]

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]

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]

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]

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]

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]

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.

 

[Peter_SSs]

Sir,
Regards. Worked beautifully.

Good to hear. Thanks for letting us know.