• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup from the row where contains maximum date.

H

hanim554

Member
Hi friends,


I have figured out below formula to search a unique value and bring the result from one colum(it can be text or number)from the row where contains max date, it is not working. any help appreciated.


INDEX(Data!C7:C25,MATCH(1,(Data!B8:B25=Home!O6)*MAX((Data!F8:F25),0)))


Data C7:25 is Name

Data!B8:B25 is Unique no.

Home O6 is Unique no.

DataF8:F25 is Date


Regards


Hanim
 
Hi, hanim554!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


It's an array formula, so remembered that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter?


Regards!
 
Dear Sir JB7,


As you instructed i have done, after serching i cant find a result suitable for me.

Below is sample data in sheet Data, in the sheet Report when i enter G60002 in D5 i want the result in E5 CVB as maximum date 30/04/2013

[pre]
Code: 
Unit N	Product	        Date out 	Ln Out	Date In	     Lctn In
G60001	GHF-34JKLJKJKF	04/04/2013	ABD	24/04/2013	CFD
G60002	GHF-34JKLJK	16/01/2012	CFD	30/04/2013	VGF
G60001	GHF-34JKLJKJKF	23/01/2012	VGF	26/01/2010	VGF
G60004	HJR-87877KJ	24/01/2012	SHG	24/04/2013	CBD
G60002	GHF-34JKLJK	25/01/2012	FTD	30/04/2013	CVD
G60002	GHF-34JKLJK	26/01/2012	CFD	23/02/2013	CVB
[/pre]
Regards

Hanim
 
There are two Lctn In that come up to your example's criteria, one is VGF and other CVD. Why not VGF?
 
Thanks for your reply Faseeh.


If I am searching G60002 there are three location in VGF,CVD and CVB, i wanted to bring CVD in this example as date in maximum 30/04/2013 ( i want to pick the data from the row where In date is maximum)


Thanks

Hanim
 
Hi, hanim554!

Both 2nd and 5th rows of your data have a "Date In" value of 30/04/2013 which is the maximum date, that's why Faseeh and me we were waiting for your answer if you wanted to retrieve VGF (2nd row) or CVD (5th) row. Still pending.

Regards!
 
Dear SirJB7,


Sorry, its my mistake when i made sample data, in the real data same Indate will never come for same Unitno. still i prefer to retrieve 5th row.


Thanks

Hanim
 
Hi Hanim ,


Try this :


=INDEX(Data!$F$2:$F$7,MATCH(MAX(IF(Data!$A$2:$A$7=Report!D5,Data!$E$2:$E$7)),IF(Data!$A$2:$A$7=Report!D5,Data!$E$2:$E$7),0))


entered as an array formula , using CTRL SHIFT ENTER.


This will work only if there are no duplicate in dates for one unit number.


Narayan
 
Hello,

Another option:


=VLOOKUP(MAX(IF(UnitN=H2, Datein)), CHOOSE({1,2}, IF(UnitN=H2, Datein), LctnIn), 2, FALSE)

enter with Ctrl + Shift + Enter


Cheers,

-Sajan.
 
Dear Sajan,

Thanks for your solutioin, i tried but it didnt work for me.


From the below formula i am trying to add one more condtion, could any one please help me.


i wanted to add " Data!$F$2:$F$7=Report!AE5 " to the below fromula, i tried to add AND function with IF. but it didnt work.


INDEX(Data!$F$2:$F$7,MATCH(MAX(IF(Data!$A$2:$A$7=Report!D5,Data!$E$2:$E$7)),IF(Data!$A$2:$A$7=Report!D5,Data!$E$2:$E$7),0))


Regards

Hanim
 
Hi, hanim554!

I think you can't do that as the range "Data!$F$2:$F$7" is used into the formula placed in E5 to search the row for the value of "Unit N" entered in cell D5, so you can't have 2 inputs for the same element: D5 and "Report!AE5".

Regards!
 
Hi Hanim ,


I have not tested this , but try :


=INDEX(Data!$F$2:$F$7,MATCH(MAX(IF((Data!$A$2:$A$7=Report!D5)*(Data!$F$2:$F$7=Report!AE5),Data!$E$2:$E$7)),IF((Data!$A$2:$A$7=Report!D5)*(Data!$F$2:$F$7=Report!AE5),Data!$E$2:$E$7),0))


again entered as an array formula , using CTRL SHIFT ENTER.


Narayan
 
Dear Narayan,


Dazzling..!!! it worked for me. thank you so much.


My hearty congratulation on occasion of crossing your post 4000.


Best regards

Hanim
 
You must log in or register to reply here.
Back
Top