Finding strings from a list between 2 string conditions that are part of the list

Question

In Google Sheets, I have a list of unique strings that I want to filter based on two conditions. These conditions are two strings within that list. Ultimately, I want to find all the strings, inclusive of my conditions, that are in between.

This is the list in the Google Sheet:

List
Apple
Banana
Pear
Grapes
Kiwi
Mandarin
Papaya
Orange
Tomato
Plum
Watermelon

Based on these two following conditions, I'm trying to get strings that fall in between these two, including these two conditions.

String 1	String 2
Pear	Orange

Ideally, the results will be displayed as an array, that way, the single formula can dynamically change the results based on the 2 conditions.

Expected Results
Pear
Grapes
Kiwi
Mandarin
Papaya
Orange

In terms of error handling, I need to account for 3 type of scenarios:

1. Scenario 1: Error handling when selected conditions not in list order

Conditions:

String 1	String 2
Plum	Kiwi

Expected Results
Nothing

--> Nothing between string selection as String 2 is BEFORE String 1 condition in the list order.

2. Scenario 2: Error handling when String conditions match

Conditions:

String 1	String 2
Mandarin	Mandarin

Expected Results
Mandarin

--> the list of strings are unique, so it will show only one result as it's inclusive of string conditions.

3. Scenario 3: Error handling when String condition #2 is empty, therefore choosing all results post-String 1 that are not empty.

Conditions:

String 1	String 2
Mandarin

Expected Results
Mandarin
Papaya
Orange
Tomato
Plum
Watermelon

Attempts:

I've tried the following 2 formulas but unfortunately I get an error as strings are not considered as numbers in the QUERY function, and the OFFSET attempt doesn't allow me to enter in between.

1.=QUERY(A4:A14,"SELECT Col1 WHERE Col1 >= '"&C4&"' AND Col 1 <= '"&D4&"'")

2.=OFFSET(A4:A14, MATCH(C4, A4:A14, 0), 0)

Testing Google Sheet link with public edit access: here

Image of current worksheet:

Answer 1

Try the following in F4:

=let(
list,tocol(A4:A,1),
string1,match(C4,list,0),
string2,ifna(match(D4,list,0),counta(list)),
iferror(chooserows(list,sequence(string2-string1+1,1,string1))))

We are using match the find the positions of the two search strings in the list, then (with appropriate error handling to deal with your described scenarios), using these positions with sequence inside chooserows to show the appropriate rows.

Answer 2

Uses inbetween() to tally the rows which fall between Pear & Orange and then filtering them as output

=let(x,A4:A14,y,row(x)-1,ifna(filter(x,isbetween(row(x),xmatch(C4,x)+y,ifna(xmatch(D4,x)+y,rows(x)+y)))))

Answer 3

Here's how to do that using a lambda to get row numbers:

=let( 
  data, A4:A14, 
  row_, lambda(d, single(filter(row(data), data = d))), 
  start, row_(C4), 
  end, if(len(D4), row_(D4), 9^9), 
  ifna(filter(data, isbetween(row(data), start, end))) 
)

See lambda().