Returning a selection of results using a lookup

[James Boulter]

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 whic
will be used to show the reference numbers.

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

I want to be able to lookup the reference numbers of all out of dat
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
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),INDEX(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

Jame

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

 

[T. Valko]

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)