Vlook up with condition

[KIM Chanthan]

Hi Dear,

Could anyone help me to solve excel problem?
Please view attached file for more detail.

Thank you very much for your kind support.
Chanthan

 

[Atlas Johnson]

Hi Chanthan,
I tried to solve your problem with a new Excel 2016 function called MINIFS.

What I basically did is;
- Find the minimum Open date for a CID
- Create a helper column for lookup based on CID and Open Date
- Make a lookup for on HELPER based on CID and MIN Open Date for that CID
- Take the rows that MIN DATE is not equal to OPEN DATE

You can combine them to one formula or even you can eliminate the helper column using MATCH / INDEX formulas.

I hope this helps,
Best,
AJ

 

[KIM Chanthan]

Hi Chanthan,
I tried to solve your problem with a new Excel 2016 function called MINIFS.

What I basically did is;
- Find the minimum Open date for a CID
- Create a helper column for lookup based on CID and Open Date
- Make a lookup for on HELPER based on CID and MIN Open Date for that CID
- Take the rows that MIN DATE is not equal to OPEN DATE

You can combine them to one formula or even you can eliminate the helper column using MATCH / INDEX formulas.

I hope this helps,
Best,
AJ

Hi,

That's almost meet my requirement, just only one point. In pink highlight cell, it should not appear anything because MFI A is not the first disbursed MFI.
One more problem is I currently use excel 2013.
It would be appreciated if you could give me another way with my excel version.

Anyway, thank for your reply.
Chanthan

 

[Atlas Johnson]

Hi Chanthan,
Sorry that it did not work for you. It is easy to fix the pink part by adding an other condition to the IF statement.

But without using MINIF, I cannot think of a way immediately on top of my head.

Sorry,
AJ

 

[KIM Chanthan]

Hi Chanthan,
Sorry that it did not work for you. It is easy to fix the pink part by adding an other condition to the IF statement.

But without using MINIF, I cannot think of a way immediately on top of my head.

Sorry,
AJ

That's why.

 

[Sophanith]

Dear Atlas Johnson,

We still have an issue regarding the file that your help. we want to figure out of the result only MFI A for more detail please find the attached file on the result that we want.