Filter Google Sheets by multiple conditions & extract

Filter and Extract Data is a one-step tool for Google Sheets that filters your data by multiple conditions and extracts all those matches to other cells in the file. When the standard Google Sheets VLOOKUP function and INDEX+MATCH are not enough, this add-on will let you easily set up more than one criteria and pull multiple values from multiple columns. It's also up to you to decide if you need the result as a formula or as values.

Video: Filter by multiple conditions and extract data

Before you start

Date and time formats

To match or lookup dates and time, make sure that the same number format is applied to the entire column with those records (Format > Number in the Google Sheets menu).

If the column contains records formatted differently, for instance, pure dates (9/27/2019) and date and time (9/27/2019 9:00 AM), the add-on won't be able to return the correct results.

Case sensitivity

Filter and Extract Data is case-insensitive, like the standard Google Sheets VLOOKUP. In other words, it doesn't distinguish values written in different cases. Thus, records like Blackberry and blackberry in your matching conditions will be treated as the same.

If you'd rather consider the text case, the workaround with INDEX+MATCH from this blog post should help you.

How to use Filter and Extract Data

Start the add-on

To run the tool, go to Extensions > Filter and Extract Data > Start in the Google Sheets menu:
Run the add-on from the Google Sheets menu.
You will see the add-on window with the settings at your service.

Tip. Power Tools also has Filter and Extract Data in its pocket. Look for the add-on icon in the Process group:
Filter and Extract Data icon in the Process group.

Specify matching conditions

For Filter and Extract Data to work, you should specify your source data table, set up your filter criteria, and decide upon the number of matches to pull:
Filter Google Sheets data by multiple conditions.

  1. Select the range with your source data.
    Tip. Pick just one cell within your table and click Auto select. The add-on will instantly highlight all cells with data (till the first blank row & column).
    Tip. Click on the Select range icon within the Source range field to specify the data you want to process.
  2. If your table columns have titles, tick off the option My table has a header. In this case, the first row will not be matched with your criteria.
  3. Choose how many rows you'd like to retrieve:
    • Pick All to find and pull all matching rows.
    • For the tool to look up only a few of the foremost records, click First and enter the number of rows you'd like to see.
  4. Here you are to decide whether to return matching values from all or only some columns of the range. Check as many individual columns as you need or keep Select all ticked off to get them all.
  5. And here goes the first condition itself: it starts with If. Its first dropdown lists all search columns of your table. Choose the one that should meet some criterion, e.g. dates that should belong to some period of time or car labels if you need the info on a specific model.
  6. The second dropdown contains operators that are used in formulas. They signal about different calculation types:
    • is empty / is not empty — find blank cells or those with data.
    • is error / is not error — find cells that either contain or don't contain any errors.
    • = (equals to) — find those cells whose content is exactly the same as the values you specify.
      Note. For the add-on to understand your conditions, it provides a special tip on how to enter data correctly. For ease of use, these formats vary based on your spreadsheet locale:
      Consult this tip to enter records to conditions correctly.
      Tip. When typing the value, press Enter on your keyboard to submit it. This way you can quickly add multiple records to the same condition.
    • (doesn't equal to) — find those cells whose content is completely different from the values you indicate.
    • > (more than) — find cells with numbers that are more than the number you enter.
    • >= (more than or equals to) — find cells with numbers that are more than or equal to the number you define.
    • < (less than) — find cells with numbers that are less than the number you provide.
    • <= (less than or equals to) — find cells with numbers that are less than or equal to your number.
    • starts with / ends with — find cells that have the same characters/strings at their beginning or end.
      Note. Currently, this operator treats everything as text strings. It wraps your record in double quotes and checks if it occurs in cells. If this doesn't suit your task with numbers, please email us (support@ablebits.com) with a sample and detailed description of your task.
    • contains / doesn't contain — find cells that contain or don't contain the records of interest.
      Note. Currently, this operator treats everything as text strings. It wraps your record in double quotes and checks if it occurs in cells. If this doesn't suit your task with numbers, please email us (support@ablebits.com) with a sample and detailed description of your task.
    • is between — find cells with numbers that are between two numeric values or with dates belonging to a certain period of time.
  7. This button lets you choose between AND or OR logic for your criteria:
    AND/OR logic for your conditions.

    • By default, all additional criteria start with and. This means that all created conditions should be met to get a match.
    • If you're okay with either of the criteria to be true for a match, pick the or option from the dropdown instead.
    Note. Whatever logic you select, it will be applied to all built conditions.
  8. If you change your mind about any сriterion, you can delete it by clicking the Close icon at the end of the criterion line.
  9. Click on the plus icon to set up more conditions.

Preview and paste the result

Once your requirements are set, you can get the result in a few different ways:
Preview the formula and paste the result.

  1. By clicking on three dots and pulling the border up or down, you can expand or shorten the preview area.
  2. Decide where to place the result: click on the Select range icon and pick the upper leftmost cell for the returned matches.
  3. Based on your conditions, the add-on builds a QUERY formula and shows it to you in this formula field. It filters your data dynamically once anything changes in the original table.
  4. To take a quick glance at the result before pasting it to your spreadsheet, hit Preview result.
  5. For the preview, all found matches from the necessary columns will be grouped at the bottom of the Filter and Extract Data window.
    Tip. You can tweak your criteria and click Preview result again to get other values.
  6. Both these buttons insert the result to your spreadsheet but in slightly different ways:
    • Click Insert formula to paste everything as the QUERY formula.
    • Or have only values returned by choosing Paste result.

Responses

Is there a way to save lookup criteria to use on other spreadsheets? I have a lot of spreadsheets that I have to sort the same way, so it would be helpful to not have to insert the conditions for each one.

Hello Madison,

Thank you for your question.

Unfortunately, in the current version of the add-on it is not possible to save lookup criteria. However, I've forwarded your request to our dev team for consideration. We will let you know if we add this feature in one of the future versions of the product. Thank you.

Hello, I wanted to know if the tool makes it possible to group column values ​​in a similar way to the 'GROUP BY' clause or if we should change it manually when we insert the formula into the spreadsheet.
I apologize if my question was not understood. I am using a translator.
From now, thank you so mucho for your attention.
Greetings!

Hi, I tried this tool Vlookup. Does it applies automation?

Like data is changing. Whenever a user is confirmed then she/he can be added automatically to the columns along with confirmed users.

Thank you

Hi Farrah,

Thank you for your question. If you want your data to be updated every time it is changed in the source data table, please select the Insert formula option when pasting the results.
In case this is not what you are asking about, please describe your task in more detail. Thank you.

Hi there, great tool by the way! Thank you so much.

I have 2 things I am struggling with

Challenge 1:

I am trying to filter out only specific names of people. Let's say I am trying to filter out only people with the name "Mark". Sheets is still returning people with the word "Market" or "Marketing" etc. Since it has the word "Mark" in it still.

I don't want that. I only want it to return if it contains the exact word, however, it could be in between one or two other words in the cell.

How can I do this?

Challenge 2: I want to be able to edit my filter after I have saved it. But I can't figure out how to go back to the original screen to be able to add other names to filter out. It restarts the process and I have to add everything from scratch again. Is there a way to add to the current list of filters so I don't have to restart every time?

Hi Zane,

Our tech specialist has reviewed your message. Yes, the condition "contains "Mark" searches for all the partial matches including Market or Marketing. Such is the logic of the add-on and Google Sheets. If you need to search names only, you have to make up a condition that restricts the search, for example, "Mark " (with a space after the name). Yet, we can't be sure in your case since we do not see how these names are entered in your data.

As for 'editing filter after you save it', for the moment our tool can not save search inquiries or recognize them from formulas either. You must enter everything anew after the restart of the add-on. BTW, we have added this feature to our improvement list.

Curious how to fix it when it says "Query completed with an empty output"?

THe preview works, simply the formula doesn't ?

Is there a way to lookup multiple numbers in a column?
I am trying to search for certain zip codes in a google sheet in 1 column..
I have 480 Postal codes I am looking for and it takes to long to enter them 1 by 1.

Can i copy and paste them in some fashion into the logic area? I tried doing this seperating with commas, semi colons and quotes around each postal code I am searching for but this just creating one long number.

Hello Dave,

Thank you for contacting us. Unfortunately, our Multiple VLOOKUP Matches will not work for the search in bulk. However, our Merge Sheets may be a great helper for this task. It updates the main table with the data from the lookup one based on a key column. Hence, if you create a small table with the zip codes, the tool will update it with the corresponding information from your original table in a few steps.

Here is the detailed manual for Merge Sheets as well just in case: https://www.ablebits.com/docs/google-sheets-merge-two-sheets/

Hope you'll find this add-on helpful!

This does exactly what I was looking for.

Now, the question is if I want to remove the matching records from the sheet how can I do that?

In Excel I am able to see the filtered results and hold down the shift key while selecting some/all of the filtered records being displayed then hit "Delete" to remove them from the sheet from which they are pulled.

Thank you,

Duane

when returning the result, is there a way to return only the specified value and not the column header?

e.g. in your "Preview and paste the result" example, returning only the values and not the column header "ID", "Model" and "Rented"

Hello Louis,

Thank you for your question.

The column headers are always displayed in the preview section by default even if there are no matches found. To paste values into the spreadsheet without the column headers, please uncheck the "My table has a header" option in the add-on window. Hope this will work for you.

Louis Meletiou says:
September 18, 2020 at 9:56 am

Thank you Katerina,

As a follow up - if in the VLOOKUP formula I want it to say IF X contains Y, and Y is text.

Can Y be a specific cell, which would allow me to copy and paste the formula automatically replacing Y?

For example My current VLOOKUP has: "and Column B contains "example text"", but if I replace that with "and Column B contains "=A2"" (where the cell A2 contains the text "example text") it does not return a result.

Hi team,

I have a list of unique IDs in a sheet and another sheet which has the main data of all unique IDs and their respective data points. Each ID in the latter has more than one row. How can I v-lookup multiple values from a column in the second sheet for each unique ID in the first one?

Hi Ammar,

Thank you for your question. We would recommend using Merge Sheets instead of Multiple VLOOKUP Matches for your task. However, the Merge Sheets tool can take only the first found entry from your lookup table at the moment. Our developers are planning to make it possible to bring all found entries.

As a workaround, we would suggest the following solution:
Step 1
please process the data in your lookup table with the Combine Duplicate Rows tool to take the values relating to the same ID to one row
Step 2
apply Merge Sheets.

For detailed information on how to work with the tools of interest, please visit our help pages:
'Combine duplicate rows in Google Sheets'
'Merge data from two Google spreadsheets'

I have installed and reloaded page, then started add on and the box comes up and it is blank, I get a short "Working" box then nothing. I am logged in on a chromebook to only 1 account, I tried signing out then back in and still not working!

How can I search a column of cells with simple addition formulas, i.e. Column A has 50 cell with various formulas like 2.3+4.5+4.1, for a specific number. The spreadsheet returns the sum of the numbers, 10.9, but I need to find out if a specific value is used in the formula in one of the 50 cells.

Katerina Bespalaya (Ablebits Team) says:
February 10, 2020 at 12:22 pm

Hello Alex,

Please try out our Advanced Find & Replace add-on for Google Sheets that can search for a specific value within formulas. You can install the add-on in a trial mode from G Suite Marketplace or directly through Google Sheets and test it out for 30 days for free to see if it suits your needs. Hope you'll find it helpful.

This addon is powerful and all, but how do I do Vlookup from another sheet?

Hello Hiro,
Thank you for your feedback.

It is possible to set another sheet as a Source range. Just run the add-on and click on the Select range icon within the Source range field. Then go to the necessary tab and select the table you want to be the source one. Hit Ok and check your Source Range field to make sure the correct table is selected.

Hi Mary,
Can I do a vlookup from another Google sheet entirely, not just another tab?
I can do this with IMPORTRANGE, e.g. =vlookup(A:A,importrange("linkofsourcespreadsheet","sheet1!A:C"),1,false)

But when I tried to paste IMPORTRANGE into the multiple vlookup matches tool I got an error

Thanks!

Hello Sam,

Thank you for your comment. Unfortunately, it is currently impossible to select a range from a different sheet. The current add-on's version works with the data within one spreadsheet only. We'll work on this case and implement the possibility to use ranges from other spreadsheets in the future, but I cannot give you any timing yet.

As a workaround, you may get the necessary range added from the other sheet into the current one with the help of the IMPORTRANGE function and run the VLOOKUP using this reference.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.