Hi everyone,
On the attached sample file, I used this array formula to lookup fo the "First purchase date" - {=MIN(IF((Sheet2!A:A=Sheet1!A2),Sheet2!B:B))}
However, for the "Second purchase" and "Third purchase", i'm stuck and can't think on how to return the dates.
Can anyone help me on this?
Thank you in advance!
Hello F,
welcome to the forum. Try this for "Second":Formula:
{=IFERROR(SMALL(IF(A2=Sheet2!A:A,Sheet2!B:B,""),2),"")}
And this for "Third":Formula:{=IFERROR(SMALL(IF(A2=Sheet2!A:A,Sheet2!B:B,""),3),"")}
Let me know if it works.
Cheers
<-- If you're happy & you know it...click the star.:-)
Hi,
Consider this alternative array formula in B2 and copy across and down:
=IF(COLUMNS($A:A)>COUNTIF(Sheet2!$A$2:$A$16,Sheet1!$A2),"",SMALL(IF((Sheet2!$A$2:$A$16=Sheet1!$A2),Sheet2!$B$2:$B$16),COLUMNS($A:A)))
I also strongly recommend you do not use entire columns for your range references within array formulas.
Regards
Re: Lookup for the succeeding dates after the earliest date
Wow, those were quick and awesome solutions!
Big help, really appreciate. Thanks a lot!
Hm. I'm finding that these formulas each return the same date for everything in the column... but that's not the goal, right?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
fbgail 
Register To Reply
Bookmarks