Try this formula in cell I3

=VLOOKUP(H3,E6:F12,2,FALSE)

Hi Sunny... i have already tried for Vlookup formula, it showing first date details i mean fruits name. based on the OEF no i have picked up the supplier committed date. i need to updated OEF & supp comit date based on fruits name.

you can see my attached.

Your latest attachment is very different from the first. Please supply the correct info in future.

There are two similar OEF no. 56061 with the same date 31/12/2017.

What is your basis to select Mango and not Banana when both have the same criterias?

Are you extracting only one fruit or many fruits based on the OEF and commitment date?

Hi Sunny

any one first fruit i have to extract based on the OEF & commitment date.

i am a material planner supplier giving dispatching date for various date. so i have to get maximum date for any one.

So you want the last item for OEF 56051 i.e. Mango instead of Banana (1st item) dated 31/12/2017?

Give this a try

=LOOKUP(2,1/(D6:D12=G3),F6:F12)

Your data must be sorted by OEF and Supplier Committed Date.

Hope this helps.

Sunny

I think I am not clearly explain you, I apologist you for wasting your time,we are using the OEF and last date (Max Date). Based on that we are finding which supplier dispatching late delivery item.

You can pick any one item which is same date with same OEF in separate item

If that is the case then this should work. It will extract 10 items (you can add more).

You will need to adjust the formulas to suit your needs.

Good luck.

Sunny

Wow.... super its working thanks you Sunny i have learn more from this site and with Sunny, Again i am thanking you.

Thanks for your feedback. Glad to know it is finally working for you.

Sunny

i have try in my data sheet but its showing error, i have data's in status sheet & i need result in summary sheet G5: Supplier name and H5: material name.

your help is great appreciate.

Try this.

Your attachment is again very different from your previous.

Sunny

Thanks got it