Duplicate look value

[excelsishya]

Hi all,

Sometimes i run into duplicate look value in financial statements.

Look value

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"><tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>C</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td>Cash</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td>AR</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td>Inv</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td>other</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>AP</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>other</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td>Equity</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td>other</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td>RE</td></tr></tbody></table>
Sheet1 (2)

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>K</td> <td>L</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td>Lookup table</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td>Cash</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>AR</td> <td style="text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>Inv</td> <td style="text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td>other</td> <td style="text-align: right;">4</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td>AP</td> <td style="text-align: right;">5</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td>other</td> <td style="text-align: right;">6</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td>Equity</td> <td style="text-align: right;">7</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td>RE</td> <td style="text-align: right;">8</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td>other</td> <td style="text-align: right;">9</td></tr></tbody></table>
i though of using following array formula

=IF(COUNTIF($C$6:$C$14,C6)>1,INDEX($L$1:$L$25,SMALL(IF(C6=$K$1:$K$25,ROW($K$1:$K$25)-ROW($K$1)+1),COUNTIF(C$6:C6,C6))),VLOOKUP($C6,$K$1:$L$25,2,0))

however many times data book will be closed, will be linked to other data book.

I read in 1 of post using countif in closed work book is problem.

Can anybody confirm this & if its true, can anybody provide alternative non array formula to accomplish above kind of duplicate lookup .
Thanks

 

[T. Valko]

Hi all,

Sometimes i run into duplicate look value in financial statements.

Look value

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: rgb(255,255,255)" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center"><TD>

</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">6</TD><TD>Cash</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">7</TD><TD>AR</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">8</TD><TD>Inv</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">9</TD><TD>other</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">10</TD><TD>AP</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">11</TD><TD>other</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">12</TD><TD>Equity</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">13</TD><TD>other</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">14</TD><TD>RE</TD></TR></TBODY></TABLE>
Sheet1 (2)

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: rgb(255,255,255)" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center"><TD>

</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">8</TD><TD>Lookup table</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">9</TD><TD>Cash</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">10</TD><TD>AR</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">11</TD><TD>Inv</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">12</TD><TD>other</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">13</TD><TD>AP</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">14</TD><TD>other</TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">15</TD><TD>Equity</TD><TD style="TEXT-ALIGN: right">7</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">16</TD><TD>RE</TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">17</TD><TD>other</TD><TD style="TEXT-ALIGN: right">9</TD></TR></TBODY></TABLE>
i though of using following array formula

=IF(COUNTIF($C$6:$C$14,C6)>1,INDEX($L$1:$L$25,SMALL(IF(C6=$K$1:$K$25,ROW($K$1:$K$25)-ROW($K$1)+1),COUNTIF(C$6:C6,C6))),VLOOKUP($C6,$K$1:$L$25,2,0))

however many times data book will be closed, will be linked to other data book.

I read in 1 of post using countif in closed work book is problem.

Can anybody confirm this & if its true, can anybody provide alternative non array formula to accomplish above kind of duplicate lookup .
Thanks

You can replace the COUNTIFs with SUMPRODUCT which works just fine when referencing a closed file.

Instead of:

COUNTIF($C$6:$C$14,C6)

Use:

SUMPRODUCT(--($C$6:$C$14=C6))

 

[Aladin Akyurek]

Is $C$6:$C$14, which CountIf references, a range in the closed workbook?

 

[excelsishya]

Biff, Thanks for the formula.

If possible can u explain what problem count if will cause in closed work book.

and also Is it possible to have non array version,
If its balance sheet array formula very fine but in income statement sometimes we need insert row array can cause problem.

Aladin,

$C$6:$C$14 is just sample look range,

i have work book 1 with say $C$6:$C$14 look value , i do lookup using above formula get answer into work book 1.the look table will be monthly statements housed in separate work books.

Then using work book 1 external link i do financial model which is sperate main work book.

during all this process i want flow to be normal. As model will sent to client .data sources in server.

Thanks all

 

[Aladin Akyurek]

Biff, Thanks for the formula.

If possible can u explain what problem count if will cause in closed work book.

and also Is it possible to have non array version,
If its balance sheet array formula very fine but in income statement sometimes we need insert row array can cause problem.

Aladin,

$C$6:$C$14 is just sample look range,

i have work book 1 with say $C$6:$C$14 look value , i do lookup using above formula get answer into work book 1.the look table will be monthly statements housed in separate work books.

Then using work book 1 external link i do financial model which is sperate main work book.

during all this process i want flow to be normal. As model will sent to client .data sources in server.

Thanks all

If the workbook housing $C$6:$C$14 which the CountIf bit addresses is closed, CountIf (as designed) cannot be used. You need something that works with array objects:

Your

COUNTIF($C$6:$C$14,C6)

can be replace with

SUM(IF($C$6:$C$14=C6,1))

Another option is:

SUMPRODUCT(($C$6:$C$14=C6)+0)

The former would be preferable in an array formula like the one you have.

 

[excelsishya]

Aladin,Thanks for providing alternatives.

i will use it in real life situation see how it behaves.

 

[T. Valko]

Biff, Thanks for the formula.

If possible can u explain what problem count if will cause in closed work book.

and also Is it possible to have non array version,
If its balance sheet array formula very fine but in income statement sometimes we need insert row array can cause problem.

If the COUNTIF function is referencing a closed file it will return the correct result UNTIL that formula recalculates then it will return a #VALUE! error.

I'm not sure I understand what you mean about:

sometimes we need insert row array can cause problem.

 

[excelsishya]

Biff Thanks for countif explanation in closed work book.

"sometimes we need insert row array can cause problem.'"

What i meant was once we use array formula to get income statement , send to clients.

sometimes they like to make some modifications.

say add row to show % of revenue in middle of table having array. since array wouldn't allow to change portion i was also looking for non array solution if any available.