Hlookup

[CraigRead]

Invoices Name2 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> </tbody>
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 Invoices Name2 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 <colgroup><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"></colgroup> <tbody> </tbody>

<colgroup><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
</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 {}.