Google Sheets - retrieving the Nth result with several matching conditions

Other Questions Google Sheets
1

I’m working with a spreadsheet that contains customer information, including names, weights, and booking dates. My goal is to create a daily manifest for certain dates that can be accessed by multiple users on a mobile app.

Customers are divided into groups (loads 1, 2, 3, etc.), with each load containing several customers and differing numbers of passengers. I need a formula that can locate the 1st, 2nd, or Nth entry that corresponds to the date in cell K3 and the load number found in cells K5, K13, and so on, and return the corresponding record from the list.

As a novice attempting to combine formulas through experimentation, I’ve tried using index matches, but I’ve encountered differences in behavior compared to Excel.

Currently, I’m using:

=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)))

This formula gets the first occurrence, and I modify the +1 for the subsequent entries. However, it only finds the initial instance where the date matches the load number, and the following rows are simply +1, +2, etc., from the first match.

Additionally, I want to note that the input sheet is populated at the time of booking and can be sorted based on date, weight, and other criteria, so maintaining a fixed order by date and load number isn’t feasible.

2

I had a similar challenge a while back when organizing data for a dynamic event schedule. Your formula concept is quite right, but rather than directly using a series of indexed modifications, you might find using an intermediary step more effective. Try using a helper column where you create a unique identifier combining date and load number. For instance, use a CONCATENATE function like =A2 & ":" & I2 for each row, aligning this with your key identifiers (cell K3 and K5/K13). Then, instead of adjusting for position manually using +1, set up your array formula to SEEK the nth occurrence by filtering this helper column. This can help pinpoint exact matches amidst the complex dataset without relying heavily on sorted order.

3

hey, you might want to try using FILTER or UNIQUE functions in Google Sheets. They can help with extracting specific data without much manual edits. Filter the rows that match your conditions, and then use INDEX to grab the nth result. Hope it helps!

4

A practical way to tackle this is to utilize Google Sheets’ QUERY function. You can structure a query that filters data based on your specific date and load number. For instance, something like =QUERY(A:I, "SELECT C WHERE A = '"&K3&"' AND I = '"&K5&'" LIMIT 1 OFFSET "&(n-1)) where n is the nth entry you’re seeking. This approach helps dynamically retrieve nth records without having to adjust formulas each time, especially helpful when data order isn’t fixed.