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>

 

[CraigRead]

i will test it out tomorrow in work. if you look at my formula in the screen shot

The results are taken from Column b7:b107

so it posts into C2:H5

not everything will be included in column B when the sheet is finalised.

 

[DanteAmor]

i will test it out tomorrow in work. if you look at my formula in the screen shot

The results are taken from Column b7:b107

so it posts into C2:H5

not everything will be included in column B when the sheet is finalised.

Just adjust the formula, change 100 to 107.
Try the new formula and tell me.

 

[CraigRead]

Sorry for the delay. it is working great.

Jut on final question is there anyway to have it return a blank cell, if it is outside h criteria. currently it is retuning #NUM in the unused cells

 

[DanteAmor]

Try this array formula



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

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.