How to compare one row of data to every row of data from another table in google sheet?

Question

Hi everyone,

I need some helps on this task. My goal is to determine whether the Status in Column I is accepted or not. If it is accepted, then print Accept in Column I and print the Actual Time Accept in Column J based on Actual Time in Column C , otherwise just leave it blank.

Rules to determine the Status(Accept):

• Forecast Time in Input greater than Time in Output
• The Price in Column G is greater than the Actual Price in Column D.

Once these 2 rules are fulfilled, then print Accept in Column I and print the Actual Time Accept in Column J based on Actual Time in Column C , otherwise just leave it blank.

As you can see from my formula in cell I3, it only compare row by row (one row in Output compare to one row in input). What I want is comparing one row of data in Output to all the rows in Input to determine whether the Status is accepted or not.

Example

For example, the 1st row in Output, the Time is 16:16:41. We compare this Time with the Forecast Time in Input. The formula start to compare from the row with Forecast Time 16:17:43 since the Forecast Time in previous rows are all smaller than 16:16:41. Since 16:17:43 greater than 16:16:41, it fulfilled the first rules. However, the Price in Output, 216.5 is smaller than the Actual Price in Input, 216.56, so the Status will still be blank. The formula will move on to the next row and check the rules again. Since the next Forecast Time, 16:18:02 is greater than 16:16:41 and the Price in Output is 216.5 greater than 216.48, all the rules fulfilled, so the Status will be Accept and the Actual Time Accept will be 21:34:00

The link for google sheet is here in case is needed: https://docs.google.com/spreadsheets/d/17coke3-oyDRLHgz79PDl3KX68kFOEte-aynVe-xEITU/edit?usp=sharing

Please give me some advice on this problem, I'm not sure this can be done without using Google Apps Script. Please let me know if you are not clear on what I'm asking about, any help will be greatly appreciated! Thank you.

Answer 1

I3 & drag down:

=IF(J3="",,"Accept")

J3 & drag down:

=IFNA(INDEX(FILTER(C$3:C, D$3:D<G3, B$3:B>H3), 1))