# concise if statements in Excel 2013 - how?

#### PKBrahma

##### New Member
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)))))))

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

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

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

##### MrExcel MVP, Moderator
Sir,
Regards. Worked beautifully.
Good to hear. Thanks for letting us know.

Replies
2
Views
87
Replies
3
Views
50
Replies
5
Views
54
Replies
6
Views
88
Replies
6
Views
95