lookup and give an array/list

I have tried using index() and match() to try and lookup a value in one column and give me the value in the corresponding row. Here is what the table looks like:

PO Number Next Date Due Status
30011-11 x/xx/xx OK
30012-11 x/xx/xx Pending
30013-11 x/xx/xx URGENT
30014-11 x/xx/xx PAST DUE

The status is based on how many days before the PO is due. I want to be able to create a column that lists which PO's are in a certain status. I tried looking up all the ones that are urgent using index and match and having it tell me the PO number but it would only give me one response. I tried using ctrl-shift enter to see if I could make an array but it would just give me an array of the same PO number. Am I missing something or is there another way to do this??

I've tried searching for something like this but I haven't seen anything using vlookup,hlookup,index,match that will give an array BUT I apologize if this is has already been answered somewhere else. I'd appreciate any help i can get and thank you ahead of time.

If your list isn't extraordinarily long, then you can try something like this

Assuming your list is in A2:C100

And your input cell is D2

then in D3 enter: =Countif(C2:C100,D2)

Then in the location you want the list to occur enter:

=IF(ROWS($A$1:$A1)>$D$3,"",INDEX(A$2:A$100,SMALL(IF($C$2:$C$100=$D$2,ROW($A$1:$A$100)-ROW($A$1)),ROWS($A$1:A1))))

Make adjustments as necessary and then confirm with CTRL+SHIFT+ENTER and copy down and across to next 2 columns

If you have more columns to copy over or your data is a lot larger, then there are other ways to be more efficient...

I really appreciate the help but what do you mean by input cell regarding cell D2? I'm not sure what I would input into this cell

That is where you enter your search criteria, e.g. URGENT

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

Show the problem[/B]

Originally Posted by ehassell01

can this be done without using arrays?

Yes... see here for an example...

http://www.excelforum.com/excel-gene...worksheet.html

Please do not continue on this thread... If you have further questions, please begin a new thread.

Please see attached

The formula in column D looks for items that are less than or equal to 5 days from today's date.

Also, I don't know if you were requesting help with the conditional formatting, so I took the liberty of doing it anyways for you.

The conditions I used were ... within 5 days -- Red
within 10 days - Amber
over 10 days away - Green

You can change as desired.