I have a Google Sheet with a number in column B and another in column C. It is a number range that is produced daily. I am trying to create a search box that a user will type in a specific number they are looking for and I need the search box to look on the sheet to see if that number falls between a number range between those columns. I have a formula that looks like this
=(IFERROR(QUERY(A5:J, "SELECT A, B, C WHERE A>='"&M8&"' AND C<='"&M8&"'",0),"NO MATCHES")). M8 refers to the Search Box, and A5:J is where the data is. The search box resides on Sheet 2. This query doesn't work the way I need it to. Is there a better function or query I could use to find if a user-inputted number falls between a number range in the sheet?

Here is the link to the sheet.....Range List Sheet

2 Answers 2


Adding to the first answer, Try to get rid of the simple quote ( ‘ ), as you trying to compare numbers you declare them as text in your query.

Try this:

<= "&M8&" ",0),"NO MATCHES"))
  • Thanks for the comment! As I previously stated in the other answer, the query still not returning results. When I put in a number that is either equal to or in between the query returns nothing except the given error message.
    – NateM
    Jun 30, 2022 at 16:29

It's hard to tell completely, but it seems you are trying to pull data from a different sheet without properly referencing it.
You have ..QUERY(A5:J,.., this means that the formula is trying to pull data from the sheet that the formula is on, not the sheet containing the values you are looking for.

Also, based on the sheet you provided, it seems you were not referencing the correct cells.

Try this instead:


  • Thanks for the comment! I honestly was referring to the sheet in an earlier attempt but more than likely I was playing around with the formula and forgot to change it back before I linked the sheet in my question. All in all, the query still doesn't work the way I need it to. When I type in a number that I know is in between the numbers in Cells B and C the results are still empty.
    – NateM
    Jun 30, 2022 at 16:27
  • @NateM Got it! You weren't referencing the correct search cell, and were comparing from the wrong column in the data sheet. No wonder nothing was matching! I have updated my answer with a formula that I have tested and know works Jun 30, 2022 at 16:39
  • 1
    It works the way I need it to. Thanks so much and sorry for any confusion I may have unintentionally caused.
    – NateM
    Jul 6, 2022 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.