# Finding preceding n results from a list based on conditions in Google Sheets

I have the following list in a 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 the preceding number of n results based on the position of the identifier in the list.

Identifier Number of Preceding Results Required
Orange 5

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

Attempts:

I've tried the following 3 formulas but unfortunately 2 return only 1 value, and I keep getting an error in the third attempt.

1.`=ARRAY_CONSTRAIN(FILTER({A3:A,ROW(A3:A)},A3:A=C3),D3,1)`

2.`=QUERY(A:A,"SELECT Col1 WHERE Col1 = '"&C3&"' LIMIT "&D3&"")`

3.`=ARRAYFORMULA(QUERY(QUERY(TO_TEXT(A3:A),"select Col1 where Col1='"&\$C\$3&"'",0),"Select * Offset " &COUNTIF(A3:A,\$C\$3)-\$D\$3))`

Testing Google Sheet link with public access: here

Image of current worksheet:

# TL;DR

```OFFSET Formula:
=OFFSET(A3:A, MATCH(C3, A3:A, 0)-D3-1, 0, D3)

Handle case where requested number exceeds available:
=LET(m,MATCH(C3, A3:A, 0)-1, IF(m=0, "first_value",
OFFSET(A3:A, MAX(m-D3, 0), 0, MIN(m, D3))))```

# OFFSET

The OFFSET function is well-suited, and can be paired with MATCH to locate your lookup value Orange, `C3` in your range:

`=OFFSET(A3:A, MATCH(C3, A3:A, 0)-D3-1, 0, D3)`

# Error Handling

The formula assumes that the lookup value `C3` exists and that there are enough preceding values to match the number requested `D3`, and there are two types of errors that may be triggered depending if (1) your lookup value doesn't exist, or (2) you request more values than available.

1. The formula will trigger an IFNA error if your lookup value is not found. Sheets' IFNA error message itself provides good info,

Error
Did not find value '[text_string]' in MATCH evaluation.

therefore handling is redundant unless you need to use the value in another formula and don't want to handle the error there. In that case you can wrap the formula in the IFNA function.

2. If the formula requests more preceding values than exist, Sheets will return an error:

Error
OFFSET evaluates to an out of bounds range.

How this is handled depends on your situation and whether you care to handle the error within the formula and, if so, whether you wish to return the available preceding values or not.

1. Don't care:
Eliminate the error state by adjusting the number of requested values to match what is available.

2. Care:
Handle the error with a simple message (or empty value), and adjust the number of requested values to match what is available. In this case I added IFNA to differentiate between the two types of errors:

```=IFERROR(
IFNA(OFFSET(A3:A, MATCH(C3, A3:A, 0)-D3-1, 0, D3), "Not_Found"),
"Exceeds_Available")```

3. Care and Return Available Values:
Adjust the formula's logic to return the available values up to and including the requested number `D3`. In this example, LET is used to store `MATCH(C3, A3:A, 0)-1` in the arbitrarily named variable `m` to avoid unnecessary code duplication. IFNA is no longer included as you can use Sheets' own message (see #1):

```=LET(m,MATCH(C3, A3:A, 0)-1,
OFFSET(A3:A, MAX(m-D3, 0), 0,MIN(m, D3)))```

• NOTE: This last will still return a `#VALUE!` error if you use the first term in your lookup range `A3:A` as your lookup value in `C3` because there would be no values that precede it (ie. `m=0`).

For completeness, this error state can be handled using an IF condition to return a custom value or a blank:
```=LET(m,MATCH(C3, A3:A, 0)-1, IF(m=0, "first_value",
OFFSET(A3:A, MAX(m-D3, 0), 0, MIN(m, D3))))```
• Thank you so much, this works perfectly! Commented Apr 13 at 6:28
• I don't think the `offset()` formula works correctly with Apple, Banana, Pear or Grapes. But it is not entirely clear what the desired results should be with those arguments because there is just the one test case. The `query()` formula does not seem correct either. Commented Apr 13 at 7:31
• @doubleunary I agree. My original answer addressed the example search but didn't account for searching for non-existent strings or requesting numbers of values that exceeded what was available. I've added an updated formula to address the second use case which seems more valuable. `=LET(m,MATCH(C3, A3:A, 0)-1, OFFSET(A3:A, MAX(m-D3, 0), 0, MIN(m, D3)))` Commented Apr 13 at 22:42
• Thank you so much for double checking regarding the issue with the less values than return and the first value. This one (=LET(m,MATCH(C3, A3:A, 0)-1, IF(m=0, "first_value", OFFSET(A3:A, MAX(m-D3, 0), 0, MIN(m, D3))))) definitely works the best. Appreciate it Commented Apr 14 at 11:41

Use `match()`, `filter()` and `isbetween()`, like this:

``````=let(
data, A3:A,
loc, match(C3, data, 0),
filter(
data,
isbetween(
sequence(rows(data)),
loc - D3, loc, true, false
)
)
)
``````
• Thank you so much, this works perfectly! Commented Apr 13 at 6:27
• I like this approach. Commented Apr 13 at 22:50