I have a sheet of customer details (names, customer weights, booking date) and I need to generate a day manifest for specific dates. May be easier in another application, but need to be accessible to many and on the go mobile app.
Customers are organised into groups (loads 1,2,3 etc), with multiple customers per load, and varying number of pax. What i'm looking for is a formula that finds the 1st, 2nd... nth record that matches the DATE queried in K3 and the LOAD number listed in K5, K13 etc., and returns the 1st, 2nd, nth record in the list.
Apologies in advance, novice trying to mash formulas together through trial and error. Tried using index matches, but the behavior in sheets is different to xls.
Trying to use:
=ARRAYFORMULA(INDEX($C:$C,SMALL(IF($A:A=$K$3,IF($I:$I=$K$5,ROW($I:$I)-ROW(INDEX($I:$I,1,1))+1)),1)))
for the first instance, then changing +1 to +2 for the second instance. However its currently just finding the first instance where date and load number match, and each successive row is just offset +1, +2 etc from the first match.
Edit - The input sheet is populated at time of booking, and the sheet may be sorted according to date, weight, etc. So the sheet cannot be kept in a date/load order unfortunately.
You can use the FILTER function.
=FILTER({C3:C,E3:E,D3:D},A3:A=K3,I3:I=K5)