lookup problems

[hipnotode]

hey, first off im using excel 2007 and windows vista
basically, im having trouble with a lookup formula, it seams to inconsistently pull what im looking for from a large table im using (830~ entries). i have checked that there are no duplicates in the table, but still seams inconsistent. is there an upper limit somewhere in the 300's for the lookup formula maybe?

xls download
the problem is in sheet 4, when the second table dropbox value is changed, only the 1 and 3-16 values pull up the corect information on the 4th table (the tables to the right of the 4th one are a bit out of date. ive been trying to fix the problem on that one.)

 

[Jonmo1]

Welcome to the board..

Hard to say without seeing your formula and some sample data.
Can you post the actual formula and some sample data?


Most likely you need to sort the data Ascending.

 

[hipnotode]

okay heres some examples of my workbook:
sheet3
table1 (item list)
<table border="1">
<tr>
<td></td>
<td>B</td>
<td>C</td>
<td>D</td>
<td>E</td>
<td>F</td>
<td>G</td>
<td>H</td>
</tr>
<tr>
<td>9</td>
<td>name</td>
<td>#</td>
<td>@</td>
<td> </td>
<td>~</td>
<td>Slot</td>
<td>Category</td>
</tr>
<tr>
<td>10</td>
<td>example1</td>
<td>1</td>
<td>=IF(H10=H9,D9+1,1)</td>
<td></td>
<td>=H10&IF(G10="Ammunition","Ammo","")&IF(D10<10,0,"")&D10</td>
<td>slot1</td>
<td>category1</td>
</tr>
<tr>
<td>11</td>
<td>example2</td>
<td>2</td>
<td>=IF(H11=H10,D10+1,1)</td>
<td> </td>
<td>=H11&IF(G11="Ammunition","Ammo","")&IF(D11<10,0,"")&D11</td>
<td>slot1</td>
<td>category2</td>
</tr>
<tr>
<td>12</td>
<td>example3</td>
<td>3</td>
<td>=IF(H12=H11,D11+1,1)</td>
<td> </td>
<td>=H12&IF(G12="Ammunition","Ammo","")&IF(D12<10,0,"")&D12</td>
<td>slot2</td>
<td>category3</td>
</tr>
<tr>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
</tr>
</table>

sheet4
table1 (category list), table2 (dropbox1) (catagory), table3 (dropbox2) (name)
<table border="1">
<tr>
<td></td>
<td>B</td>
<td>C</td>
<td>D</td>
<td>E</td>
<td>F</td>
<td>G</td>
<td>H</td>
<td>I</td>
<td>J</td>
<td>K</td>
<td>L</td>
</tr>
<tr>
<td>3</td>
<td>category</td>
<td>Slot</td>
<td>#</td>
<td></td>
<td>Dropbox#</td>
<td>Dropbox</td>
<td>category</td>
<td></td>
<td>Dropbox#</td>
<td>Dropbox</td>
<td>Name</td>
</tr>
<tr>
<td>4</td>
<td>category1</td>
<td>slot1</td>
<td>1</td>
<td> </td>
<td>1</td>
<td>1</td>
<td>catagory1</td>
<td> </td>
<td>1</td>
<td>1</td>
<td>example1</td>
</tr>
<tr>
<td>5</td>
<td>catagory2</td>
<td>slot1</td>
<td>2</td>
<td> </td>
<td>2</td>
<td>1</td>
<td>category1</td>
<td> </td>
<td>2</td>
<td>1</td>
<td>example1</td>
</tr>
<tr>
<td>6</td>
<td>category3</td>
<td>slot1</td>
<td>3</td>
<td> </td>
<td>3</td>
<td>1</td>
<td>category1</td>
<td> </td>
<td>3</td>
<td>1</td>
<td>example1</td>
</tr>
<tr>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
</tr>
</table>

table 4, name dropbox1 selection
<table border="1">
<tr>
<td></td>
<td>N</td>
<td>O</td>
<td>P</td>
<td>Q</td>
<td>R</td>
<td>S</td>
</tr>
<tr>
<td>2</td>
<td>1</td>
<td>=LOOKUP(Q2,Q4:Q54,O4:O54)</td>
<td>=LOOKUP(Q2,Q4:Q53,P5:P54)</td>
<td>=K4</td>
<td>=LOOKUP(P2,P4:P54,R4:R54)</td>
<td>=LOOKUP(P2,P4:P54,S4:S54)=LOOKUP(P2,P4:P54,S4:S54)</td>
</tr>
<tr>
<td>3</td>
<td>select</td>
<td>name</td>
<td>#</td>
<td>@</td>
<td>~</td>
<td>Select</td>
</tr>
<tr>
<td>4</td>
<td>=G4</td>
<td>=Sheet3!B10</td>
<td>=Sheet3!$C$10</td>
<td>=IF(S4=S3,Q3+1,1)</td>
<td>=S4&IF(Q4<10,0,"")&Q4</td>
<td>=IF((IF(Sheet3!H10="","",Sheet3!H10))=0,"",(IF(Sheet3!H10="","",Sheet3!H10)))</td>
</tr>
<tr>
<td>5</td>
<td>catagory</td>
<td>=IFERROR(IF(O4="","",(IF((LOOKUP(R5,Sheet3!$F$10:$F$838,Sheet3!$B$10:$B$838))=O4,"",(LOOKUP(R5,Sheet3!$F$10:$F$838,Sheet3!$B$10:$B$838))))),"")</td>
<td>=IF(P3="","",(IF((IFERROR(LOOKUP(R5,Sheet3!$F$10:$G$838,Sheet3!$C$10:$C$838),""))=P3,"",(IFERROR(LOOKUP(R5,Sheet3!$F$10:$G$838,Sheet3!$C$10:$C$838),"")))))</td>
<td>=IF(S5=S4,Q4+1,1)</td>
<td>=S5&IF(Q5<10,0,"")&Q5</td>
<td>=(IF(N$6="Select","",N$6))</td>
</tr>
<tr>
<td>6</td>
<td>=H4</td>
<td>=IFERROR(IF(O5="","",(IF((LOOKUP(R6,Sheet3!$F$10:$F$838,Sheet3!$B$10:$B$838))=O5,"",(LOOKUP(R6,Sheet3!$F$10:$F$838,Sheet3!$B$10:$B$838))))),"")</td>
<td>=IF(P5="","",(IF((IFERROR(LOOKUP(R6,Sheet3!$F$10:$G$838,Sheet3!$C$10:$C$838),""))=P5,"",(IFERROR(LOOKUP(R6,Sheet3!$F$10:$G$838,Sheet3!$C$10:$C$838),"")))))</td>
<td>=IF(S6=S5,Q5+1,1)</td>
<td>=S6&IF(Q6<10,0,"")&Q6</td>
<td>=(IF(N$6="Select","",N$6))</td>
</tr>
<tr>
<td>7</td>
<td> </td>
<td>=IFERROR(IF(O6="","",(IF((LOOKUP(R7,Sheet3!$F$10:$F$838,Sheet3!$B$10:$B$838))=O6,"",(LOOKUP(R7,Sheet3!$F$10:$F$838,Sheet3!$B$10:$B$838))))),"")</td>
<td>=IF(P6="","",(IF((IFERROR(LOOKUP(R7,Sheet3!$F$10:$G$838,Sheet3!$C$10:$C$838),""))=P6,"",(IFERROR(LOOKUP(R7,Sheet3!$F$10:$G$838,Sheet3!$C$10:$C$838),"")))))</td>
<td>=IF(S7=S6,Q6+1,1)</td>
<td>=S7&IF(Q7<10,0,"")&Q7</td>
<td>=(IF(N$6="Select","",N$6))</td>
</tr>
<tr>
<td>...</td>
<td> </td>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
<td>...</td>
</tr>
</table>

 

[hipnotode]

bump