Formula not working

fari1 · Aug 26, 2011

hi,
i've two formulas which work together, one finds the rows numbers for the cells, that contain my required text strings, while other bring in the value for those text strings, the problem is, the row address formula is finding the duplicate values, instead of giving just one address, its giving address of my 2nd string to find result two times, i dont know why,

Sheet1 <table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:122px;" /><col style="width:59px;" /><col style="width:507px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >String to Find 1</td><td style="text-align:left; ">CONSOLIDATED</td><td style="text-align:right; ">114</td><td style="text-align:left; ">CONSOLIDATED BALANCE SHEETS </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >String to Find 2</td><td style="text-align:left; ">ACCOMPANYING</td><td style="text-align:right; ">176</td><td style="text-align:left; ">See accompanying notes to unaudited consolidated financial statements. </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="text-align:right; ">176</td><td style="text-align:left; ">See accompanying notes to unaudited consolidated financial statements. </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td style="text-align:right; ">185</td><td style="text-align:left; ">UNAUDITED CONSOLIDATED STATEMENTS OF INCOME </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td style="text-align:right; ">242</td><td style="text-align:left; ">See accompanying notes to unaudited consolidated financial statements. </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td style="text-align:right; ">242</td><td style="text-align:left; ">See accompanying notes to unaudited consolidated financial statements. </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td style="text-align:right; ">251</td><td style="text-align:left; ">UNAUDITED CONSOLIDATED STATEMENTS OF SHAREHOLDERS’ EQUITY </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td style="text-align:right; ">271</td><td style="text-align:left; ">See accompanying notes to unaudited consolidated financial statements. </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td style="text-align:right; ">271</td><td style="text-align:left; ">See accompanying notes to unaudited consolidated financial statements. </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td style="text-align:right; ">280</td><td style="text-align:left; ">UNAUDITED CONSOLIDATED STATEMENTS OF CASH FLOWS </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td style="text-align:right; ">335</td><td style="text-align:left; ">See accompanying notes to unaudited consolidated financial statements. </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td style="text-align:right; ">335</td><td >See accompanying notes to unaudited consolidated financial statements. </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td style="text-align:right; ">344</td><td >NOTES TO UNAUDITED CONSOLIDATED FINANCIAL STATEMENTS </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td style="text-align:right; ">348</td><td >The accompanying consolidated financial statements include the accounts of Chicago Pizza & Brewery, Inc. and its wholly owned subsidiaries. The consolidated financial statements have been prepared in accordance with accounting principles generally accepted in the United States, and with the instructions to Form 10-Q and Rule 10-01 of Regulation S-X. </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td style="text-align:right; ">348</td><td >The accompanying consolidated financial statements include the accounts of Chicago Pizza & Brewery, Inc. and its wholly owned subsidiaries. The consolidated financial statements have been prepared in accordance with accounting principles generally accepted in the United States, and with the instructions to Form 10-Q and Rule 10-01 of Regulation S-X. </td></tr></table> <table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >Spreadsheet Formulas</td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D2</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr><tr><td >C3</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D3</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr><tr><td >C4</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D4</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr><tr><td >C5</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D5</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr><tr><td >C6</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D6</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr><tr><td >C7</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D7</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr><tr><td >C8</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D8</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr><tr><td >C9</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D9</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr><tr><td >C10</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D10</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr><tr><td >C11</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D11</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr><tr><td >C12</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D12</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr><tr><td >C13</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D13</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr><tr><td >C14</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D14</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr><tr><td >C15</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D15</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr><tr><td >C16</td><td >{=IF(ISERROR(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)),"",SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}</td></tr><tr><td >D16</td><td >{=IF(ISERROR(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))),"",INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}</td></tr></table></td></tr><tr><td >Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!</td></tr></table> Excel tables to the web - Excel Jeanie Html 4

the formula for row address is in column C which is

Code:

{(SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1))}

and the formula that gives values for this address is in column D which is

Code:

{(INDEX(list,SMALL(IF(ISERROR(SEARCH(search_strings,TRANSPOSE(list))),"",TRANSPOSE(ROW(list)-MIN(ROW(list))+1)),ROW(list)-MIN(ROW(list))+1)))}

the list and search_strings are the named ranges. the list is the named range for the data out which the text strings needed to be find, which is sheet2$A2$:$A$5000

and the 2nd named range search_strings is the named range for string to find, which is sheet1$B$2:$B$3

fari1 · Aug 26, 2011

any ideas

Andrew Poulsom · Aug 26, 2011

The problem arises because some phrases contain both words. Sorry, I don't know a solution.

fari1 · Aug 26, 2011

any alternate idea??

Formula not working

fari1

Active Member

Excel Facts

fari1

Active Member

Andrew Poulsom

MrExcel MVP

fari1

Active Member

Similar threads

Forum statistics

Share this page

Formula not working

fari1

Active Member

Excel Facts

fari1

Active Member

Andrew Poulsom

MrExcel MVP

fari1

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock