"danioma" wrote:
I am having trouble with a lookup formula and was hoping someone could
help me. I have one list of invoices one on sheet and a column next to
them where it says paid or not paid. I am trying to pull only the "not
paid" amounts into another sheet but want to do this without the zeroes
or spaces in between rows. Any ideas how to do this?

One way using non-array formulas ..

Assume source data is in sheet: X,
in cols A to C, with the key status col = col C (with the "Not Paid" text)
data from row 2 down to a max expected row 100 (say)

In another sheet: Y (say)

Put in A2:
=IF(ISERROR(SMALL($C:$C,ROW(A1))),"",INDEX(X!A:A,M ATCH(SMALL($C:$C,ROW(A1)),$C:$C,0)))
Copy A2 to B2

Put in C2: =IF(X!C2="","",IF(X!C2="Not Paid",ROW(),""))
(Leave C1 empty)

Select A2:C2, fill down to C100
(cover the expected data extent in X)

Cols A & B will auto-return only the lines with "Not Paid" in X,
with all results neatly bunched at the top (w/o spaces !)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---