Video: how to vlookup multiple values in Google Sheets

This video tutorial shows the easiest way to vlookup by multiple criteria in Google Sheets. The add-on finds all matches and pulls data from another sheet as values or as ready-made Google Sheets QUERY formula.

How to vertically lookup multiple matches in Google Sheets: video transcript

If you ever tried to look up matches in Google Sheets and pull related data, you know it’s a challenge to do it easy and right.

Can you quickly get the names of all students who passed the test? What about one more condition – only those who studied Spanish? Actually, let’s take dates into account as well: get only those who took the Spanish test in March. Also, can you pull entire rows with the results to a new sheet?

Problems of looking for multiple matches with Google Sheets formulas

Normally, you turn to standard Google Sheets functions designed for this purpose. But building formulas from scratch can become a real struggle in no time; especially if the exact number of matches is undefined.

  • You can start solving the problem with Google Sheets VLOOKUP: worry about proper sorting and the number of columns in a table. And all these to only discover that this function cannot look at its left and fetches one match only.
    Google Sheets VLOOKUP cannot look at its left and fetches only one match.
  • The FILTER function may look easier, but it doesn't pull data to another sheet and requires nested IF to search by more than one criteria. Looks not so easy after all.
  • INDEX MATCH seems promising, but in most cases, it should be wrapped in array formulas. And sadly, practice shows that you are most likely to get confused in its arguments.
  • As for the QUERY function, it uses an absolutely different peculiar language. To build or edit such formula, you'll have to go through the instructions on Docs Editors Help every time.

Why Multiple Vlookup Matches is a better solution

When you’re ready to give up this fight against Google Sheets formulas, our Multiple Vlookup Matches will become your favourite sidekick.

You don't have to be already prepared for this add-on: it's simple, fast, and does the hardest job for you.

Having this tool at hand, you will quickly set and edit multiple criteria in bite-sized parts and in plain English – without any wildcards or other special characters.

You will also enter dates, time and other data types the usual – human way, without any extra functions. User-friendly ways of creating conditions for empty cells or even partial text matches is another good addition.

The add-on will show you the result preview if you need, and pull all matches to another sheet.
Set conditions the easy way and get all matches.

If all these sound too good to be true, let’s see the add-on in action.

Run the tool and select your data range

Go to the Add-ons menu to find the tool: you can have it either as part of Power Tools > Process group, or as a separate tool.

As soon as you start it, the add-on auto-detects your source data. If your table has a header, make sure the corresponding option is selected.

Decide what to return

Then, you need to decide on the number of rows and columns for the result:

  1. pick All to return all matching rows or choose First and limit the number of lines to return
  2. the next drop-down lets you select all or particular columns to pull the related records from

Set up conditions

When it is decided, it's time to set up search criteria:

  1. Start by picking the column with those values, that should meet your first requirement.
  2. The next drop-down is a list of special operators that are used to build criteria. Select one, and the tool will invite you to type in the values you're looking for. Since the add-on always prompts on how the values should look, you will type in several records painlessly no matter their format – even dates and time.
    The add-on prompts how to enter values of different formats.
  3. To create more conditions, click the plus icon and tweak the same set of options. With Multiple Vlookup Matches, you are free to specify dozens of criteria and adjust or remove them on the go.
  4. You're also in control of making either all or at least one of the conditions count to get a match. This drop-down will let you change the AND/OR logic for your entire formula.

Preview the result

The bottom part of the add-on is all about the result.

Click the icon in the first field to choose a place for the outcome data. You can pick the upper leftmost cell in one of your existing sheets, or add a new sheet and select a cell there.

The Preview result button lets you take a look at the selection made by all those conditions you've just built. You can expand the area for your convenience, scroll down all found rows and see the formula that stands behind them.
Preview the entire result and a formula.

If everything looks fine, there are two ways of fetching this data to your sheet. This blue button will insert the formula from the above preview to the selected destination cell. The other one – Paste result – will return all matching data as values. Just click one of the buttons, and get all matches in a new table.

You can install this powerful add-on from the store as a single tool or as part of Power Tools. And if you still have any questions – please contact us, we'll be happy to help!

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)