Forum Discussion
Returning max value from vector result based on lookup function
Hi
Im trying to return the maximum value from one column based on the value in a second equalling a specific value in a third (actually a separate sheet)
| Sheet 1 | LocationInfo | Sheet 2 | AnalysisResults | ||||
| SampleSite | ValueMax | X | Y | SampleSite | Depth | Value | |
| A-01 | 0.5 | 0.0000 | 0.0000 | A-01 | 2 | 0.1 | |
| A-02 | 1.2 | 0.0000 | 0.0000 | A-01 | 4 | 0.5 | |
| A-03 | 0.5 | 0.0000 | 0.0000 | A-02 | 1 | 0.1 | |
| A-04 | 1.0 | 0.0000 | 0.0000 | A-02 | 2 | 1.2 | |
| A-02 | 5 | 0.8 | |||||
| A-03 | 3 | 0.5 | |||||
| A-04 | 1 | 0.2 | |||||
| A-04 | 2 | 0.5 | |||||
| A-04 | 5 | 1.0 | |||||
| A-04 | 6 | 0.4 | |||||
Bassicaly in the locationInfo sheet i want to have the maximum analysis value (in red) from value column in AnalysisResults sheet whereby it chooses the value when SampleSite column matches between the two sheets.
Ive tried using lookup, but just cant get it to select the max value.
While the data is on one sheet above, in reality it is in two.
Office 365, latest excel and windows
many thanks Andy
2 Replies
=MAXIFS('AnalysisResults'!C2:C10,'AnalysisResults'!A2:A10,A2:A5)
type in sheet LocationInfo. Try thisIn B2 on LocationInfo:
=MAXIFS(AnalysisResults!$B$2:$B$100, AnalysisResults!$A$2:$A$100, A2)
Fill down.
