Type a merchant into one cell, say swiggy, and a QUERY with contains or like in the where clause returns every transaction whose description includes it. Wrap the column in lower() and the search text in LOWER() so the capital letters in your statement do not matter.
How do I set up the search box?
Assume dates in A, narration in B, amount in C and category in D, with headers in row 1. Put the search word in F1. Then in F3 enter:
- =QUERY(A1:D500, "select A, B, C, D where lower(B) contains '"&LOWER(F1)&"' order by A desc", 1)
The formula joins pieces of text. The query string ends just after the opening single quote, the part &LOWER(F1)& drops in your search word, and the string resumes with the closing single quote. If F1 holds swiggy, QUERY sees where lower(B) contains 'swiggy'.
What is the difference between contains and like?
| Operator | Example | Matches |
|---|---|---|
| contains | lower(B) contains 'swiggy' | Anywhere in the text |
| like | lower(B) like '%swiggy%' | Same, with % as the wildcard |
| starts with | lower(B) starts with 'upi' | Only at the beginning |
| ends with | lower(B) ends with 'bank' | Only at the end |
| matches | lower(B) matches '.*(swiggy|zomato).*' | A pattern covering the full text |
Contains is case-sensitive in the query language, which is why the lower() wrapper matters. Without it, a narration written as SWIGGY would not match the search word swiggy.
The like form needs the percent signs inside the query: lower(B) like '%"&LOWER(F1)&"%'. The matches form needs to match the entire cell, so wrap your pattern with .* at both ends, or you will get no rows even when the word appears inside the text.
How do I search for two merchants at once?
Use matches with a bar between the names, for example swiggy|zomato. Type that into F1, and use:
- =QUERY(A1:D500, "select A, B, C, D where lower(B) matches '.*("&LOWER(F1)&").*' order by A desc", 1)
With two typed names, any transaction mentioning either one appears.
What happens when nothing matches?
QUERY may return an error saying the output was empty. Wrap the whole formula so the sheet says something kinder:
- =IFERROR(QUERY(A1:D500, "select A, B, C, D where lower(B) contains '"&LOWER(F1)&"' order by A desc", 1), "No matching transactions")
Can I add up what I find?
Yes, with a second formula beside the first. For example, if the search shows three transactions of 320, 450 and 275 rupees, the total is 320 plus 450 plus 275, which is 1,045.
- =IFERROR(QUERY(A1:D500, "select sum(C) where lower(B) contains '"&LOWER(F1)&"' label sum(C) 'Total'", 1), 0)
Quick fixes if the search finds nothing
- Look at one narration and copy a stretch of it into F1 to confirm spelling.
- Check there are no extra spaces in F1.
- Confirm the range covers all the rows you expect.
- Remember bank narrations may shorten or alter merchant names, so try a shorter word.
Can I search two columns at once?
Yes, join two conditions with or. To look for the search word in either the narration or the category, use where lower(B) contains '"&LOWER(F1)&"' or lower(D) contains '"&LOWER(F1)&"'. If you also add a date filter with and, wrap the two or conditions in brackets so the filter applies to both. Without the brackets, the and and or are combined in a way you may not intend, and rows outside your dates can slip in.
If you search the same few merchants every week, categorising them once is better than searching again. SpendixAI lets you set your own rules and add merchant aliases for imported statements, so similar narrations land in the same place.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


