Leverage your VLOOKUP experience with the QUERY function
Multiple Vlookup Matches scans your entire table and fetches those records that match the conditions you set. Unlike the regular Google Sheets VLOOKUP function, the utility brings back each and every matching record and puts them into a new table. All you need to do is select the range and tweak the requirements. The tool will build a QUERY formula for you and paste it into the desired place.Try it for 30 days for free
This add-on for spreadsheets comes in handy when you want to check multiple columns for different matches according to several conditions at once. It brings back all matches in one go and equips you with a Google Sheets QUERY formula that you can adjust to your liking.
In contrast to Google Sheets filter, this add-on lets you set up all conditions within one plain window and preview the result while making quick adjustments at the same time.
Also, Multiple Vlookup Matches won't mess with your source data. It will create another table wherever you choose.
As cheesy as it may sound, our add-on takes your experience with formulas to the next level. We use an advanced version of VLOOKUP — QUERY function. So no nested/array formulas or helper columns are needed anymore.
While standard Google Sheets VLOOKUP cannot look at its left, QUERY looks over the entire range at once: up, down, to the left, and to the right of the column(s) with your condition(s).
Unlike both VLOOKUP and INDEX+MATCH that fetch only single record at a time, our Multiple Vlookup Matches returns a range of all relevant values together. You can even decide to simply pull records from the source table or insert everything as a ready-made QUERY formula.
Run Multiple Vlookup Matches from the Add-ons tab and do the following:
See a more detailed description here.
Not at all :)
Just set the necessary conditions using 14 built-in comparison operators and let Multiple Vlookup Matches do the rest.
Absolutely not. Your skills and experience with formulas don't matter.
The options are designed in the most simple way: you tweak the conditions using common comparison operators such as equal to, is between, contains, etc., and Multiple Vlookup Matches does the rest.
There are 13 different comparison operators to help you out.
Some will check if the value is equal to, less than, more than, or not equal to some numbers.
Others will get the info belonging to some period of time or number sets (is between).
You can also check all records for any part of specific chars, words, or numbers (contains/doesn't contain). Or go see if matches are blank or not or have errors in cells or not.
As many as you need. And even for different data types — all for one lookup.
All of them, really. Or the number of some first occurrences you specify.
No, Multiple Vlookup Matches searches for all occurrences, no matter their text case.
Of course. Multiple Vlookup Matches contains Preview area that shows not only the formula beforehand but also the result.
It will, but only if you paste the result as a formula.