Vlookup multiple matches based on multiple criteria

Multiple Vlookup Matches is a one-step tool that finds those records in your Google Sheets that correspond to the required conditions. When the standard Google Sheets VLOOKUP is not enough, use this add-on to quickly set up more than one criteria and return multiple matches from multiple columns. It's also up to you to decide if you need the result as a formula or as values.

Before you start

Multiple Vlookup Matches is case-insensitive, like the standard Google Sheets VLOOKUP. In other words, it doesn't distinguish values written in different cases. Thus, records like Volkswagen and volkswagen in your matching conditions will be treated as the same.

If you'd rather consider the text case, the workaround with INDEX+MATCH described here should help you.

How to use Multiple Vlookup Matches

Start the add-on

To run the tool, go to Add-ons > Multiple Vlookup Matches > Start in the Google Sheets menu:
Run the tool from the spreadsheet menu.
You will see the add-on window with the settings at your service.

Specify matching conditions

For Multiple Vlookup Matches to work, you should specify your source table, set up your lookup criteria, and decide upon the number of matches to pull:
Specify where to look and what to return.

  1. Select the range with your source data.
    Tip. Pick just one cell within your table and click Auto select. The add-on will identify the entire used range for you.
    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 your table. 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 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 equal to) - find those cells whose content is exactly the same as the values you specify.
      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.
      Note. At the moment, this operator doesn't work with dates. To search for cells with certain dates, please use between dates instead.
    • (is not equal to) - find those cells whose content is completely different from the values you specify.
      Note. This operator doesn't work with dates. To search for cells excluding certain dates, please use between dates instead.
    • >= (more than or equal to) - find cells with numbers that are more than or equal to the number you specify.
    • > (more than) - find cells with numbers that are more than the number you enter.
    • < (less than) - find cells with numbers that are less than the number you enter.
    • <= (less than or equal to) - find cells with numbers that are less than or equal to the number you specify.
    • between dates - find cells with dates belonging to a certain period of time.
    • is between - find cells if numbers in them are between two numeric values you specify.
    • contains/doesn't contain - find cells that contain or don't contain the records of interest.
      Note. Currently, this operator doesn't work with dates. If you're not sure of a workaround, please email us (support@ablebits.com) with a sample and description of your task.
    • 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.
  7. Click on the plus icon to set up more 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.

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 tool builds a QUERY formula and shows it to you in this formula field. It updates itself dynamically once anything changes in your criteria.
  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 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.

Related pages

Ask a question (posted publicly)

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.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.