Hlookup

[CraigRead]

OK here goes. this is a quick example of what i am trying to do. table size A1:E11 Invoices is in A1. I would like to be able to copy the name from B6:B11 to the cells under Name 2 if they are at 2 weeks delay So in C2:E3 under name2 i would like to be able to populate this with the Names from Name1 if they are at 2 weeks
I would like it to look like this if possible <colgroup><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"></colgroup> <tbody> [TD="width: 86"]Invoices

<colgroup><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
[TD="width: 86"]

Priority	3
Late	3
order	name1	weeks
1	one tree	2
2	two tree	1
2	one cab	2
4	two cab	1
5	one car	1
6	two car	2

<colgroup><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
[TD="width: 86"]Invoices[/TD]
[TD="width: 86"]
[/TD]
[TD="width: 257, colspan: 3"]Name2
[/TD]

</tbody>

[/TD]
[TD="width: 86"][/TD]
[TD="width: 257, colspan: 3"][/TD]

Priority Late	3	one tree	one cab	two car
Late	3
order	name1	weeks
1	one tree	2
2	two tree	1
2	one cab	2
4	two cab	1
5	one car	1
6	two car	2

[TD="width: 86"][/TD]
[TD="width: 257, colspan: 3"]Name2
[/TD]

</tbody>

[/TD]

</tbody>

 

[DanteAmor]

Try this array formula.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></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><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Invoices</td><td > </td><td style="background-color:#ffc000; ">Name2</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Priority Late</td><td style="text-align:right; ">3</td><td >one tree</td><td >two tree</td><td >one car</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Late</td><td style="text-align:right; ">3</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#92d050; ">order</td><td style="background-color:#92d050; ">name1</td><td style="background-color:#92d050; ">weeks</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">1</td><td >one tree</td><td style="text-align:right; ">2</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">2</td><td >two tree</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">2</td><td >one cab</td><td style="text-align:right; ">2</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">4</td><td >two cab</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">5</td><td >one car</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">6</td><td >two car</td><td style="text-align:right; ">2</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></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 >Zelle</td><td >Formel</td></tr><tr><td >C2</td><td >{=INDEX($B$6:$B$11,SUM(SMALL(IF(($C$6:$C$11=2)*ROW($C$6:$C$11)=ROW($C$6:$C$11),ROW($C$6:$C$11)),COLUMNS($C$1:C1)))-ROW($B$6))}</td></tr></table></td></tr></table>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Then copy the formula to the right.

 

[DanteAmor]

Edit:

Use this array formula


{=INDEX($B$6:$B$11,SMALL(IF(($C$6:$C$11=2)*ROW($C$6:$C$11)=ROW($C$6:$C$11),ROW($C$6:$C$11)),COLUMNS($C$1:C1))-ROW($B$5))}

 

[CraigRead]

thank you. i will test it out. i paste the formula into cells c2:e2

 

[DanteAmor]

Paste the formula in cell C2, then edit the formula, to accept, press the 3 keys: Shift + Control + Enter

If entered correctly, Excel will surround with curly braces {}.

Then copy the formula to the right.

 

[CraigRead]

OK got this working in row C2:F2. now i want to expand this to down to row C5. i pasted and edited the formula but it displays the same results in row C2-C5

 

[DanteAmor]

OK got this working in row C2:F2. now i want to expand this to down to row C5. i pasted and edited the formula but it displays the same results in row C2-C5

To update the formula you have to explain in detail how you have your data.

 

[CraigRead]

[/IMG]

 

[DanteAmor]

In C2: H2 are the results from 1 to 6.
In C3H3 do you want the results from 7 to 12?

 

[DanteAmor]

If the above is correct, try the following:



{=INDEX($B$7:$B$100,SMALL(IF(($C$7:$C$100=2)*ROW($C$7:$C$100)=ROW($C$7:$C$100),ROW($C$7:$C$100)),COLUMNS($C$1:C1)+(ROW(B1)-1)*6)-ROW($B$6))}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself


Then copy the formula to the right then copy down.