Excel 2003 ... What I have

Range A4:A103 ... ascending values 1, 2, 3, to 100
Range B4:J103 ... unique line-item records

Range K4:K5003 ... ascending values 1, 2, 3, to 100 (each repeat 50 times)

Range L4:T5003 ... I need a formula to return each of the line-item records
contained in Range B4:J103 (each 50 times)

I am using a VLOOKUP formula with a chg to return Col only, but I am having
the following issues:

=VLOOKUP($K4,$A$3:$J$5003,4,0) ... returns "0" when an empty cell is found
(I do not wish to see a "0" ... I want an empty cell) ... so I started using

=if(VLOOKUP($k4,$A$3:$J$5003,4,0)="","",VLOOKUP($K 4,$A$3:$J$5003,4,0)) ...
Looks great, but now my "Sort" fails as the "" found in the cell ends @ the
top ... I wish it to be @ bottom. If I then Filter Blanks, Clear Contents,
Show ALL, & re-sort all is fine.

What do I need to do here?

My "Thanks" in advance to those of you that are intimate with Excel &
support these boards ... Kha

Perhaps we can "cheat". Since the truly blank cells get identified as 0's via
formulas, how important is it to have the formula cell be "blank". Perhaps we
can simply hide the zero.

If you don't want any zeros, there's the regular Tools-Options-View, hide
zero ability.

Another lesser known option would be to format the cells with formula to his
custom format (or something similar):
#;#;;@

Custom formats follow this syntax:
{postive};{negative};{zero};{text}
By stating what you want to show for each section, you can have more
control. If you need 2 decimal places, can do:
#.00;#.00;;@

Also, you didn't state it, but I'm assuming that the normal value returned
is an alphanumeric value (otherwise the zero would still be at the top).

The final alternative might be to create a custom sort order...
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ken" wrote:

Excel 2003 ... What I have

Range A4:A103 ... ascending values 1, 2, 3, to 100
Range B4:J103 ... unique line-item records

Range K4:K5003 ... ascending values 1, 2, 3, to 100 (each repeat 50 times)

Range L4:T5003 ... I need a formula to return each of the line-item records
contained in Range B4:J103 (each 50 times)

I am using a VLOOKUP formula with a chg to return Col only, but I am having
the following issues:

=VLOOKUP($K4,$A$3:$J$5003,4,0) ... returns "0" when an empty cell is found
(I do not wish to see a "0" ... I want an empty cell) ... so I started using

=if(VLOOKUP($k4,$A$3:$J$5003,4,0)="","",VLOOKUP($K 4,$A$3:$J$5003,4,0)) ...
Looks great, but now my "Sort" fails as the "" found in the cell ends @ the
top ... I wish it to be @ bottom. If I then Filter Blanks, Clear Contents,
Show ALL, & re-sort all is fine.

What do I need to do here?

My "Thanks" in advance to those of you that are intimate with Excel &
support these boards ... Kha