VLookup + If condition

[denniswtlx]

I am trying to figure out a formula that I don't believe is too complex, I just can't resolve it. Here is a very simplified version of my table to give you the general idea.

Item Event Date
A100 Ordered 07/01/2013
A100 Shipped 07/03/2013
B101 Ordered 06/23/2013
B101 Shipped 06/24/2013
C100 Ordered 07/15/2013
C100 Shipped 07/20/2013

I want to look up the Order Date for anything shipped and put that date in col 4.

[Craig K.]

Try this

Please Login or Register  to view this content.

make sure the cells with this formula are formatted to be Date

[Fotis1991]

Give a try to this ARRAY formula.

=IFERROR(INDEX($C$2:$C$10,SMALL(IF($B$2:$B$10="Shipped",ROW($B$2:$B$10)-1),ROW(C1))),"")



-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[denniswtlx]

=IF(B3="ordered","",SUMPRODUCT((A:A=A3)*(B:B="ordered"),C:C)) produces the following.
Item Event Date
A100 Ordered 7/1/2013 7/1/2013
A100 Shipped 7/3/2013
B101 Ordered 6/23/2013 6/23/2013
B101 Shipped 6/24/2013
C100 Ordered 7/15/2013 7/15/2013
C100 Shipped 7/20/2013 1/0/1900

=IFERROR(INDEX($C$2:$C$10,SMALL(IF($B$2:$B$10="Shipped",ROW($B$2:$B$10)-1),ROW(C1))),"") produces the following:

Item Event Date
A100 Ordered 7/1/2013 7/3/2013
A100 Shipped 7/3/2013 6/24/2013
B101 Ordered 6/23/2013 7/20/2013
B101 Shipped 6/24/2013
C100 Ordered 7/15/2013
c100 Shipped 7/20/2013

What I am trying to produce is this:

Item Event Date Ordered
A100 Ordered 7/1/2013
A100 Shipped 7/3/2013 7/1/2013
B101 Ordered 6/23/2013
B101 Shipped 6/24/2013 6/23/2013
C100 Ordered 7/15/2013
c100 Shipped 7/20/2013 7/15/2013

[Craig K.]

My equation if you started in cell D3 and copied down should provide you with the Solution you were looking for.

Can you upload your copy of the workbook with our equations put in?

[denniswtlx]

It works if there is only 1 product ID with Ordered and 1 Shipped. But if the same product number was ordered and shipped twice, then I run into the formula not working. Test file attached.

[Craig K.]

Unless you have another field that is a unique identifier for each order (not just the item#), there's no way that I know of to get what you want. If the orders are always grouped together ("ordered" record directly above the "shipped" record) you can just set the equation to equal the cell above.