• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

VLookup using multiple criteria question|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / VLookup using multiple criteria question|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…VLookup using multiple criteria que…
sp_PrintTopic sp_TopicIcon
VLookup using multiple criteria question
Avatar
Rich Gomberg

New Member
Members
Level 0
Forum Posts: 2
Member Since:
November 27, 2018
sp_UserOfflineSmall Offline
1
November 27, 2018 - 11:58 am
sp_Permalink sp_Print

I am working on a weather dashboard.  I did a pivot table for the max and min temperature by day.  On the dashboard tab, when you enter the date in cell D5, it returns the max and min temperature.  I also want it to show the time of the max and min temperature (cells F5 and H5).  The SumIfs function works if there is only one instance of the max (or min) temperature in a day.  For example, on 9/18/18, the temperature of 87.6 occurs 4 times.  As a result, it is summing all 4 times.  I am looking for a formula that will essentially do a VLookup based on date and temperature that will return the time of the first instance it meets both criteria.  Using the 9/18/18 example, the time it should return for the max temperature is 12:24PM.

sp_AnswersTopicSeeAnswer See Answer
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
November 27, 2018 - 1:57 pm
sp_Permalink sp_Print

Hi Rich

Please refer attached.

I use a VLOOKUP combined with a PivotTable.

Please note that your original formulas in cell E5 and G5 are not correct as they just search for the first entry which may not be the Max/Min Temperature.

Furthermore the range covered is only up to rows 1038.

Hope this helps.

Sunny

sp_AnswersTopicAnswer
Answers Post
Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 625
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
3
November 27, 2018 - 6:23 pm
sp_Permalink sp_Print

For your max temp time:

=AGGREGATE(15,6,1/1/((acuriteweather[Date]=D5)*(acuriteweather[Outdoor Temperature]=E5))*acuriteweather[Timestamp],1)

For the min temp time:

=AGGREGATE(15,6,1/1/((acuriteweather[Date]=D5)*(acuriteweather[Outdoor Temperature]=G5))*acuriteweather[Timestamp],1)

 

Also, since you have a pivot table, you can use GETPIVOTDATA for the Min and Max temps:

=IFERROR(GETPIVOTDATA("High",TempPivot!$A$3,"Date",D5,"Months",MONTH(D5)),"")

=IFERROR(GETPIVOTDATA("Low",TempPivot!$A$3,"Date",D5,"Months",MONTH(D5)),"")

Avatar
Rich Gomberg

New Member
Members
Level 0
Forum Posts: 2
Member Since:
November 27, 2018
sp_UserOfflineSmall Offline
4
November 29, 2018 - 12:01 pm
sp_Permalink sp_Print

Velouria said
For your max temp time:

=AGGREGATE(15,6,1/1/((acuriteweather[Date]=D5)*(acuriteweather[Outdoor Temperature]=E5))*acuriteweather[Timestamp],1)

For the min temp time:

=AGGREGATE(15,6,1/1/((acuriteweather[Date]=D5)*(acuriteweather[Outdoor Temperature]=G5))*acuriteweather[Timestamp],1)

 

Also, since you have a pivot table, you can use GETPIVOTDATA for the Min and Max temps:

=IFERROR(GETPIVOTDATA("High",TempPivot!$A$3,"Date",D5,"Months",MONTH(D5)),"")

=IFERROR(GETPIVOTDATA("Low",TempPivot!$A$3,"Date",D5,"Months",MONTH(D5)),"")  

Velouria, when I use the AGGREGATE function you outlined, I get a #VALUE! return.  What does the 1/1((accurite...) represent?  I understand the rest of the formula.  Thanks.

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 625
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
5
November 30, 2018 - 7:13 pm
sp_Permalink sp_Print

This part:

((acuriteweather[Date]=D5)*(acuriteweather[Outdoor Temperature]=G5))

creates an array of 1s and 0s. Multiplying by the time stamp returns an array of time values and zeroes. Since we want the minimum, we don't want the 0 values in there, so 1 divided by that array, will return an array of fractions and #DIV/0 errors. Finally we divide 1 by that array again, to invert the fractions (the errors remain unchanged). Passing 6 as the second argument to AGGREGATE tells it to ignore errors, so we get the minimum time value, not 0 or an error.

 

Here's your file with the formulas in place.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Kim Knox, Kevin Lenahan
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Forum Stats:
Groups: 3
Forums: 24
Topics: 6221
Posts: 27285

 

Member Stats:
Guest Posters: 49
Members: 31908
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.