One play which gets you there, Steve ..

Assume in sheet: dataprior,
you have your formula in H2 down:
=IF($A20,IF(ISERROR(MATCH($B2,dataID,0)),"No Longer Here",""),"")

Then in a new sheet,
Put in A1:
=IF(dataprior!H2="","",IF(dataprior!H2="No Longer Here",ROW(),""))

[Here, we could also have used the simpler: =IF(dataprior!H2="","",ROW()) in
A1 since the only indications evaluated in col H in dataprior would be either
"" or "No Longer Here".]

Put in B1:
=IF(ROW()COUNT(A:A),"",INDEX(dataprior!A:A,SMALL( A:A,ROW())+1))
Select A1:B1, copy down to cover the max expected extent of col H in
dataprior. Hide away col A. You'd get the results you seek in col B, all
neatly bunched at the top. Do likewise to extract it for "New Employee".
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steve" wrote:
I have the below on 2 separate data sheets, indicating if an employee is no
longer here or if a new employee. These are in column H of the data sheets.

=IF($A4130,IF(ISERROR(MATCH($B413,dataID,0)),"No Longer Here",""),"")

=IF(A5140,IF(ISERROR(MATCH($B514,PriorDataID,0)), "New Employee",""),"")

If a no longer here result is displayed in dataprior!H413, can I get the
cell value of A413 to display in another sheet, such as in cell B1 for the
first incident , the next in B2, ? The no longer here, and new employee
results are very random, and will be in various rows.

Thanks,

Steve