# Improving my Formula

#### Cooki

##### New Member
Hi All

ive created this formula and it works perfect, but as your see in a sec its rather big.

Can anyone think of a smarter way to get the same result.

The below checks to see if there is a date in the BOLDED cell and if there is it moved onto the next till it cant find a date, when it cant find a date it returns that columns name. There are 29 columns and as you can see by the formula i look at every second column apart from 12.

The formula is on the front page of the database

I use named ranges to do my checks in P8 (Partners names) and C8 which referenced my 4 different sheets

=IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),2,0)=0,INDIRECT(C8&"!C2"),IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),4,0)=0,INDIRECT(C8&"!E2"),IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),6,0)=0,INDIRECT(C8&"!G2"),IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),8,0)=0,INDIRECT(C8&"!I2"),IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),10,0)=0,INDIRECT(C8&"!K2"),IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),14,0)=0,INDIRECT(C8&"!O2"),IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),16,0)=0,INDIRECT(C8&"!Q2"),IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),18,0)=0,INDIRECT(C8&"!S2"),IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),20,0)=0,INDIRECT(C8&"!U2"),IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),22,0)=0,INDIRECT(C8&"!W2"),IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),24,0)=0,INDIRECT(C8&"!Y2"),IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),26,0)=0,INDIRECT(C8&"!AA2"),IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),28,0)=0,INDIRECT(C8&"!AC2"),"Management_Fee_Completed")))))))))))))

Preview Table

 Partners Finance_Q1 Finance_Days_Q1 Comm_Val_Q1 Comm_Days Finance_State_Q1 Fiance_2_Days Partner_1 01/01/19 Partner_2 01/01/19 01/01/19

<tbody>
</tbody>

#### Special-K99

##### Well-known Member
Is there some reason why

IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),12,0)=0,INDIRECT(C8&"!M2"),

doesnt appear in that formula ?

Last edited:

#### Cooki

##### New Member
I dont need bring back that information.

Its really just im proving this formula =IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),2,0)=0,INDIRECT(C8&"!C2") and both the highlighted being increased by 2

4 = E
6 = G
8 = I
10 = K
SKIP 12
14 = O

And so on till 28 = AC