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 function and INDEX+MATCH are not enough, use this add-on to quickly set up more than one criteria, vlookup to the left as well, 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.
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.
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 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.
To run the tool, go to Extensions > Multiple VLOOKUP Matches > Start in the Google Sheets menu:
You will see the add-on window with the settings at your service.
For Multiple VLOOKUP Matches to work, you should specify your source data table, set up your lookup criteria, and decide upon the number of matches to pull:
Once your requirements are set, you can get the result in a few different ways:
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.
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.
Thank you for your question. For us to be able to help you better, please send us a few screenshots showing the sample data you have and the result you want to get at email@example.com. We'll see if our software can help.
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 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
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?
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 ?
Thank you for your comment. Sorry to hear that you are having difficulties of this kind.
For us to understand the problem better and help you fix it, we've sent you an email requesting some additional information. Please email us back with the requested details at firstname.lastname@example.org. Thank you.
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.
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 for your comment. You may take the very same steps in Google Sheets - filter rows by a particular value(s) and remove the duplicate records manually.
If however, you'd like to get rid of all the repeating values in a click, use our Remove Duplicate Cells add-on instead. I believe it'll be helpful.
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"
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.
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.
Thank you for the follow-up, Louis.
I have just replied to your query by email. If you have any other questions, please feel free to email back.
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?
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:
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
apply Merge Sheets.
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!
Thank you for your comment. We have just replied to you via email. Please provide us with the requested information and we’ll do our best to help. Thank you.
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.
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?
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.
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
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!