• 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 MIN and MAX from ranges of data

Kazebear

New Member
Hi All,
I am just starting to dabble in excel and I just know that there are ways to do things in my head, but not sure how to execute them in excel. Please help.
What I am attempting to do is to do a VLOOKUP from one sheet based on a column of data and look up the min and max dates based on the data in column 1 in the first sheet.
Hope this makes sense....not down with the excel lingo yet...
On the attached file, sheet, MIN MAX DATES is where I wish to pull in the values based on column A. The LOOKUP data is sitting in sheet POID ID, Column B
Thank you!
Karen
 

Attachments

  • MIN MAX DATES V2.xlsx
    846.9 KB · Views: 8
Perhaps with these aggregate formulae
B2:=AGGREGATE(15,6,'POID ID'!$B$2:$B$20470/('POID ID'!$A$2:$A$20470='MIN MAX DATES'!$A2),1)
C2:=AGGREGATE(14,6,'POID ID'!$B$2:$B$20470/('POID ID'!$A$2:$A$20470='MIN MAX DATES'!$A2),1)
 

Attachments

  • Copy of MIN MAX DATES V2.xlsx
    887 KB · Views: 2
If Office 365 subscription, use the brand new MAXIFS function. If not, use the solution offered above.
 
Or………..

In B2, copied right to C2 and all copied down :

=IFERROR(AGGREGATE(IF(B$1="MIN",15,14),6,'POID ID'!$B$2:$B$20470/($A2='POID ID'!$A$2:$A$20470),1),"")

Regards
Bosco
 

Attachments

  • MIN MAX DATES V2(1).xlsx
    889 KB · Views: 2
Or………..

In B2, copied right to C2 and all copied down :

=IFERROR(AGGREGATE(IF(B$1="MIN",15,14),6,'POID ID'!$B$2:$B$20470/($A2='POID ID'!$A$2:$A$20470),1),"")

Regards
Bosco
Another one......

In B2, enter formula :

=IFERROR(AGGREGATE({15,14},6,'POID ID'!$B$2:$B$20470/($A2='POID ID'!$A$2:$A$20470),1),"")

then,

Select B2:C2 >> Ctrl+Shift+Enter >> Select B2:C2 again >> positioning the cursor on the lower right corner of the cell and Double Clicking >> Finish

Regards
Bosco
 
Or this simple alternative with Power Query.
Just need to get rid off those "#Value" in case id. Added a step replace errors by null.
upload_2018-7-19_11-43-4.png
 

Attachments

  • Copy of MIN MAX DATES V2.xlsx
    939 KB · Views: 0
Hi All,
I am just starting to dabble in excel and I just know that there are ways to do things in my head, but not sure how to execute them in excel. Please help.
What I am attempting to do is to do a VLOOKUP from one sheet based on a column of data and look up the min and max dates based on the data in column 1 in the first sheet.
Hope this makes sense....not down with the excel lingo yet...
On the attached file, sheet, MIN MAX DATES is where I wish to pull in the values based on column A. The LOOKUP data is sitting in sheet POID ID, Column B
Thank you!
Karen
If Office 365 subscription, use the brand new MAXIFS function. If not, use the solution offered above.
Thank you AliGW, it worked like magic!
 
Glad to have helped! Just bear in mind that MAXIFS will not work if you share the file with anyone who does not have the Office 365 subscription version. :)
 
Back
Top