CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Владимир Владимирович
Владимир Владимирович

Reputation: 107

Which functions I need to use for this type of Google Sheets search?

I have the list of dates A and list of prices B.

Then manually filled search range in D and E.

I need to perform a search for number, that will be higher than G, or lower than H.

As result we show founded date in J. If no matching number is found, return E. Price (G or H), that triggered successful result in L. And founded price M, just B from date J.

enter image description here

Which functions can help me to implement this type of search? I tried to use INDEX, FILTER, but can't properly set the range like IF "HIGHER THAN" or "LOWER THAN" on every cell.

The main target is gradually checking each cell vertically, one by one, searching for a price, that will be higher or lower than sought. And if the number was not found, return the end date of the search.

Added the Google Sheets link, so you can test your solution and compare it. https://docs.google.com/spreadsheets/d/1gmw7I778MGfCZENsOos4HhKB07X0wLp7fKs9hyn0a-Q/edit?usp=sharing

Upvotes: 0

Views: 85

Answers (2)

basic
basic

Reputation: 11968

You can use following formulas:

for Expected result:

=IFERROR(INDEX($A$14:$A$23;MATCH(1;((D14<=$A$14:$A$23)*(E14>=$A$14:$A$23)*(((G14<=$B$14:$B$23)+(H14>=$B$14:$B$23))>0));0));E14)

for Triggered price:

=CHOOSE(1+(M14>=G14)+(M14<=H14)*2;"None";G14;H14)

for Founded price:

=IFERROR(INDEX($B$14:$B$23;MATCH(1;((D14<=$A$14:$A$23)*(E14>=$A$14:$A$23)*(((G14<=$B$14:$B$23)+(H14>=$B$14:$B$23))>0));0));ARRAYFORMULA(MAX($B$14:$B$23*(E14=$A$14:$A$23))))

enter image description here

Upvotes: 1

Oleg_S
Oleg_S

Reputation: 161

See sheet "Search formula test" in your file.

To return date following formula is used

    =IFERROR(IFERROR(INDEX(FILTER($A$14:$B;$A$14:$A>=D14;$A$14:$A<=E14;$B$14:$B>=G14);1;1);
INDEX(FILTER($A$14:$B;$A$14:$A>=D14;$A$14:$A<=E14;$B$14:$B<=H14);1;1));
MAX(FILTER($A$14:$B;$A$14:$A>=D14;$A$14:$A<=E14)))

Where it filters dates based on "Search from" / "Search until" data and Price check, first for "Higher than" then if no values found - for "Lower than".

Date result is returned with INDEX(filterFormula;1;1).

Last part MAX(FILTER()) returns last date in checked range in case no values were found.

For price the same formula is used but INDEX(filterFormula;1;2) returns price and last part VLOOKUPs price for last date in checked range.

However, there is a problem with using formulas as it first checks selected range for one condition and then for next one. Better solution would be script to check each cell in selection for both conditions.

Upvotes: 1

Related Questions