A more complicated case of looking up values in a table

Yamezz

Active Member
Joined
Nov 22, 2006
Messages
372
Office Version
  1. 365
I have a table of raw grape analysis results, for which I need to create a summary table that includes a calculated harvest date.
DateSampleBaumépHTA
29/1/25SHZ11.653.118.1
29/1/25FIO10.62.8614.3
29/1/25VER9.752.9810.5
29/1/25GRN11.52.969.1
29/1/25CAS11.83.19.3
3/2/25SHZ13.553.237.3
3/2/25FIO12.652.9710.6
3/2/25VER11.153.137.9
3/2/25GRN12.853.087.9
3/2/25CAS12.63.197.7
6/2/25CAF12.533.147
6/2/25SHZ13.73.215.9

In order to calculate the harvest date, I need to know the current speed of ripening. To calculate the speed of ripening, I need to know the most recent analysis and the second-most-recent analysis. To do this, I need Excel to find within the instances of "FIO" (for example) the Baume from the most recent analysis (and subsequently the second-most-recent analysis).

I can work out how to get the most recent date with a MAX function, but in this case, that would return a date of 6th-Feb, which is not valid as there is no FIO analysis for 6-Feb.

How do I return the Baume where Sample = "FIO" and Date is the last date of any FIO sample?
In this case the Date would be 3rd Feb and the resultant Baume 12.65
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
May be something like this,
Book1
ABCDEFG
1DateSampleBaumépHTA
229-JanSHZ11.653.118.1FIO Latest
329-JanFIO10.62.8614.33-Feb
429-JanVER9.752.9810.512.65
529-JanGRN11.52.969.1
629-JanCAS11.83.19.3
73-FebSHZ13.553.237.3
83-FebFIO12.652.9710.6
93-FebVER11.153.137.9
103-FebGRN12.853.087.9
113-FebCAS12.63.197.7
126-FebCAF12.533.147
136-FebSHZ13.73.215.9
Sheet2
Cell Formulas
RangeFormula
G3G3=INDEX(A2:A13, MATCH(1, (B2:B13="FIO")*(A2:A13=MAXIFS(A2:A13, B2:B13, "FIO")), 0))
G4G4=INDEX(C2:C13, MATCH(1, (B2:B13="FIO")*(A2:A13=MAXIFS(A2:A13, B2:B13, "FIO")), 0))
 
Upvote 0
@Yamezz not sure if the Excel version in the profile is correct. If you have MO 365 you can use the following:

* cell H2 and H3 allow you to select sample type of variable measured
* if your data has more rows, just change the range in input1 (currently A1:E13)

Book3
ABCDEFGH
1DateSampleBaumépHTA
229/01/2025SHZ11.653.118.1Search 1CAF
329/01/2025FIO10.62.8614.3Search 2pH
429/01/2025VER9.752.9810.5Result6/02/2025
529/01/2025GRN11.52.969.1
629/01/2025CAS11.83.19.3
73/02/2025SHZ13.553.237.3
83/02/2025FIO12.652.9710.6
93/02/2025VER11.153.137.9
103/02/2025GRN12.853.087.9
113/02/2025CAS12.63.197.7
126/02/2025CAF12.533.147
136/02/2025SHZ13.73.215.9
Sheet1
Cell Formulas
RangeFormula
H4H4=LET( input1, A1:E13, input2,SORT(CHOOSECOLS(FILTER(input1,CHOOSECOLS(input1,2)=H2),1,2,XMATCH(H3,TAKE(input1,1))),1,-1), input3, ROWS(input2), input4, DROP(input2,1-input3,-2), input4 )
Cells with Data Validation
CellAllowCriteria
H2List=$B$2:$B$13
H3List=$C$1:$E$1
 
Upvote 0
Looks like you are using a formal Excel table so perhaps this is what you are after.
I have assumed that there would not be more than one result per day per grape variety & that you may want to look up different varieties and different measures.

Yamezz.xlsm
ABCDEFGHIJKLMN
1SampleMeasure
2DateSampleBaumépHTASampleFIOCAFBaumé
329/01/2025SHZ11.653.118.1MeasureBauméCASpH
429/01/2025FIO10.62.8614.3FIOTA
529/01/2025VER9.752.9810.5DateBauméGRN
629/01/2025GRN11.52.969.1Last3/02/202512.65SHZ
729/01/2025CAS11.83.19.32nd Last29/01/202510.6VER
83/02/2025SHZ13.553.237.3
93/02/2025FIO12.652.9710.6
103/02/2025VER11.153.137.9
113/02/2025GRN12.853.087.9
123/02/2025CAS12.63.197.7
136/02/2025CAF12.533.147
146/02/2025SHZ13.73.215.9
15
16
Sheet1
Cell Formulas
RangeFormula
K5K5=J3
J6:J7J6=AGGREGATE(14,6,Table1[Date]/(Table1[Sample]=J$2),ROWS(J$6:J6))
K6:K7K6=AGGREGATE(14,6,INDIRECT("Table1["&J$3&"]")/((Table1[Sample]=J$2)*(Table1[Date]=J6)),1)
Cells with Data Validation
CellAllowCriteria
J2List=$M$2:$M$7
J3List=$N$2:$N$4


If I change the variety & measure in J2:J3 (via Data Validation options in those cells)

Yamezz.xlsm
ABCDEFGHIJKLMN
1SampleMeasure
2DateSampleBaumépHTASampleSHZCAFBaumé
329/01/2025SHZ11.653.118.1MeasurepHCASpH
429/01/2025FIO10.62.8614.3FIOTA
529/01/2025VER9.752.9810.5DatepHGRN
629/01/2025GRN11.52.969.1Last6/02/20253.21SHZ
729/01/2025CAS11.83.19.32nd Last3/02/20253.23VER
83/02/2025SHZ13.553.237.3
93/02/2025FIO12.652.9710.6
103/02/2025VER11.153.137.9
113/02/2025GRN12.853.087.9
123/02/2025CAS12.63.197.7
136/02/2025CAF12.533.147
146/02/2025SHZ13.73.215.9
15
16
Sheet1
Cell Formulas
RangeFormula
K5K5=J3
J6:J7J6=AGGREGATE(14,6,Table1[Date]/(Table1[Sample]=J$2),ROWS(J$6:J6))
K6:K7K6=AGGREGATE(14,6,INDIRECT("Table1["&J$3&"]")/((Table1[Sample]=J$2)*(Table1[Date]=J6)),1)
Cells with Data Validation
CellAllowCriteria
J2List=$M$2:$M$7
J3List=$N$2:$N$4
 
Upvote 0
Solution
I am assuming that your actual data may contain more than two or three dates of testing. Given that, you could use power query to achieve your expected results.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Sample"}, {{"Count", each _, type table [Date=nullable date, Sample=text, Baumé=number, pH=number, TA=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "Sample", "Baumé", "pH", "TA", "Index"}, {"Date", "Sample", "Baumé", "pH", "TA", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [Index] <= 2),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Sample] = "FIO")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows1",{"Date", "Sample", "Baumé"})
in
    #"Removed Other Columns1"
 
Upvote 0
Book1
ABCDEFGHIJKLM
1DateSampleBaumépHTA
21/29/2025SHZ11.653.118.1FIO LatestDateSampleBaumépHTA
31/29/2025FIO10.62.8614.32/3/2025FIO12.652.9710.6
41/29/2025VER9.752.9810.5
51/29/2025GRN11.52.969.1
61/29/2025CAS11.83.19.3
72/3/2025SHZ13.553.237.3
82/3/2025FIO12.652.9710.6
92/3/2025VER11.153.137.9
102/3/2025GRN12.853.087.9
112/3/2025CAS12.63.197.7
122/6/2025CAF12.533.147
132/6/2025SHZ13.73.215.9
Sheet2
Cell Formulas
RangeFormula
H3:L3H3=FILTER(A2:E13,A2:A13=MAXIFS(A2:A13,B2:B13,LEFT(G2,3))*(B2:B13=LEFT(G2,3)))
Dynamic array formulas.
 
Upvote 0
Thankyou @Peter_SSs. Can you explain to me what the "Table1[Date]/(Table1[Sample]=J$2)" is doing to achieve the match we're looking for? Also, why did you use ROWS() instead of a single numeral?
 
Upvote 0
Can you explain to me what the "Table1[Date]/(Table1[Sample]=J$2)" is doing to achieve the match we're looking for?
Table1[Date]/(Table1[Sample]=J$2) will produce an array of ..
- If the sample is the sample of interest the result will be the date
- If the sample is not the sample of interest the result will be an error
So the results of the blue section above will be the dates where the sample is the one we are interested in and errors otherwise. The AGGREGATE function then chooses the maximum (= last) date for the non-error values.

Also, why did you use ROWS() instead of a single numeral?
No significant reason other than we can just copy the first of those formulas to the second row. We could just as easily have used the single numeral 1 for the the first cell and the single numeral 2 for the second cell.
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,135
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top