I'm trying to find away to return a reference number using a lookup.

I have 2 work sheets one where the info is stored and the other which will be used to show the reference numbers.

The main sheet (list) works out the difference between 2 dates (in theory showing reference numbers of items out of date) and returns the number of days.

I want to be able to lookup the reference numbers of all out of date items and have them shown in one list on the second sheet (MI).

At the moment i've been able to get it to return references based on 1 day out of date. I need it to be all out of date items.

any one have any ideas. I've attached the file i've been working on

here is the array i've been using, this was given to me play with.

=IF(ROWS($1:1)<=COUNTIF(List!$C$5:$C$100,$B$11),IN DEX(List!$D$5:$D$100,SMALL(IF(List!$C$5:$C$100=$B $11,ROW(List!$C$5:$C$100)-MIN(ROW(List!$C$5:$C$100))+1),ROWS($1:1))),"")

Thank you

James

What's in B11?

The COUNTIF function is using a comparison of =B11 but the inner IF is using
a compariosn of =B11. They need to be the same.

Try this slighly re-arranged version. I've made the comparison to B11 to be
= in both locations.

=IF(ROWS(A$1:A1)<=COUNTIF(List!$C$5:$C$100,"="&$B $11),INDEX(List!$D$5:$D$100,SMALL(IF(List!$C$5:$C$ 100=$B$11,ROW(List!$C$5:$C$100)),ROWS(A$1:A1))-MIN(ROW(List!$C$5:$C$100))+1),"")

Also, don't forget to array enter. Array formulas need to be entered using
the key combination of CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"James Boulter" wrote in message
...

I'm trying to find away to return a reference number using a lookup.

I have 2 work sheets one where the info is stored and the other which
will be used to show the reference numbers.

The main sheet (list) works out the difference between 2 dates (in
theory showing reference numbers of items out of date) and returns the
number of days.

I want to be able to lookup the reference numbers of all out of date
items and have them shown in one list on the second sheet (MI).

At the moment i've been able to get it to return references based on 1
day out of date. I need it to be all out of date items.

any one have any ideas. I've attached the file i've been working on

here is the array i've been using, this was given to me play with.

=IF(ROWS($1:1)<=COUNTIF(List!$C$5:$C$100,$B$11),IN DEX(List!$D$5:$D$100,SMALL(IF(List!$C$5:$C$100=$B $11,ROW(List!$C$5:$C$100)-MIN(ROW(List!$C$5:$C$100))+1),ROWS($1:1))),"")

Thank you

James


+-------------------------------------------------------------------+
|Filename: sheet.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=122|
+-------------------------------------------------------------------+



--
James Boulter