"SHAWN" wrote...
....
I had troubles figuring out how to accomplish this without VBA and
Harlan Grove has kindly offered a solution to my problem, which is an
array formula, that looks like this:

{=IF(NOT(ISERROR(VLOOKUP(A6,W2Ord,1,FALSE))),
VLOOKUP(A6,W2Ord,1,FALSE),IF(A6="",INDEX(W2Ord,
MATCH(0,COUNTIF(C$5:C5,W2Ord),0)),""))}
....

That's not my formula.

You've screwed it up with VLOOKUPs that I didn't use. Note that if the
initial VLOOKUP in the IF condition doesn't return an error, THERE'S
ABSOLUTELY NO BENEFIT FROM CALLING IT A SECOND TIME! In other words,

=IF(ISNUMBER(VLOOKUP(A6,W2Ord,1,0)),A6,...)

because nonerror VLOOKUP(x,y,1,0) == x by necessity.

*IF* your W2Ord list contained only distinct numeric values sorted in
ascending order, you could use (assuming the status entry corresponding to
A6 should be in B6)

B6:
=IF(ISNUMBER(A6),IF(A6<MIN(W2Ord),"",IF(VLOOKUP(A6 ,W2Ord,1)=A6,A6,"")),
IF(B5<MAX(W2Ord),INDEX(W2Ord,MATCH(B5,W2Ord)+1),"" ))

filled down. This could be made more efficient by calculating MIN(W2Ord) and
MAX(W2Ord) in other cells and referring to those cells rather than calling
MIN and MAX in each col B formula.

If W2Ord isn't sorted or contains duplicate numeric values which shouldn't
be duplicated in the status column in the report, then the only improvement
you may be able to achieve would be something like

B6 [*array* formula]:
=IF(ISNUMBER(A6),IF(A6<MIN(W2Ord),"",IF(ISNUMBER(M ATCH(A6,W2Ord,0)),A6,"")),
IF(B5<MAX(W2Ord),MIN(IF(W2OrdB5,W2Ord)),""))

This assumes ISNUMBER(MATCH(x,y,0)) will recalc faster than COUNTIF(y,x) and
that MIN(IF(yz,y)) will recalc faster than
INDEX(y,MATCH(0,COUNTIF(.,y),0)).

If you're going to go for a programatic solution, read List2 into an array,
keep track separately of its MIN and MAX values, and sort it. Then loop
through your List1 checking its values against List2's MIN value and using
binary search to find closest matches in List2. Once the final value in
List1 has been matched, dump the rest of the stored and sorted List2 into
the cells below the last matched value. Don't just use any formula in
batches of cells.