UNLIMITED_VLOOKUP
Multi-criteria VLOOKUP that matches across multiple columns simultaneously.
=UNLIMITED_VLOOKUP(searchValue, searchRange, returnColumn, [value2, column2, ...])Gibt zurück: any
Übersicht
UNLIMITED_VLOOKUP is a powerful enhancement to Google Sheets' native VLOOKUP function that supports matching on multiple criteria at once. While the standard VLOOKUP can only search a single column, UNLIMITED_VLOOKUP allows you to specify additional value/column pairs to narrow down your search across as many columns as needed. This makes it ideal for complex datasets where a single column is not enough to uniquely identify a row, such as looking up a product price by both product name and size, or finding an employee's salary by both department and role.
Parameter
| Parameter | Typ | Erforderlich | Beschreibung |
|---|---|---|---|
searchValue | any | Ja | The primary value to search for in the first column of the range. |
searchRange | Range | Ja | The data range to search through (e.g., A1:F100). |
returnColumn | number | Ja | The column number (1-indexed) from which to return the result. |
value2, column2, ... | any, number, ... | Nein | Optional additional value/column pairs for multi-criteria matching. Pass alternating values and column numbers. |
Beispiele
Single criteria product lookup
Find "Laptop" in column 1 and return the value from column 4 (price).
=UNLIMITED_VLOOKUP("Laptop", A1:D10, 4)Eingabe
| Product | Category | Stock | Price |
| Mouse | Accessories | 150 | $25 |
| Laptop | Computers | 30 | $999 |
| Keyboard | Accessories | 200 | $75 |
Ausgabe
$999Two-criteria employee lookup
Find the row where column 1 is "Sales" AND column 3 is "Manager", then return column 4 (salary).
=UNLIMITED_VLOOKUP("Sales", A1:D8, 4, "Manager", 3)Eingabe
| Department | Name | Role | Salary |
| Sales | Alice | Associate | $55,000 |
| Sales | Bob | Manager | $85,000 |
| Engineering | Carol | Manager | $110,000 |
Ausgabe
$85,000Three-criteria inventory lookup
Find the row matching product "T-Shirt", color "Blue", and size "Large" to get the stock count from column 5.
=UNLIMITED_VLOOKUP("T-Shirt", A1:E20, 5, "Blue", 2, "Large", 3)Eingabe
| Product | Color | Size | SKU | Stock |
| T-Shirt | Red | Large | TS-R-L | 42 |
| T-Shirt | Blue | Medium | TS-B-M | 67 |
| T-Shirt | Blue | Large | TS-B-L | 23 |
Ausgabe
23Grade lookup by student and subject
Look up Emma's grade in Math by matching both name in column 1 and subject in column 2, returning column 4.
=UNLIMITED_VLOOKUP("Emma", A1:D12, 4, "Math", 2)Eingabe
| Student | Subject | Semester | Grade |
| Emma | English | Fall | A- |
| Emma | Math | Fall | A+ |
| James | Math | Fall | B+ |
Ausgabe
A+Shipping rate by origin, destination, and method
Look up shipping cost by matching origin country, destination region, and shipping method across three columns.
=UNLIMITED_VLOOKUP("US", A1:E15, 5, "EU", 2, "Express", 3)Eingabe
| Origin | Destination | Method | Days | Cost |
| US | EU | Standard | 14 | $9.99 |
| US | EU | Express | 5 | $29.99 |
| US | Asia | Express | 7 | $34.99 |
Ausgabe
$29.99Anwendungsfälle
Inventory management
Look up product stock levels by matching product name, warehouse location, and size variant simultaneously.
Employee records
Find employee compensation data by matching department, role, and seniority level together.
Order fulfillment
Retrieve shipping cost by matching origin region, destination region, and shipping method in a rate table.
Student grade lookups
Find a student's grade by matching student name, course code, and semester in a consolidated gradebook.
Real estate comparisons
Look up comparable property prices by matching neighborhood, number of bedrooms, and property type.
Profi-Tipps
Use cell references instead of hardcoded values for search criteria to make your formulas dynamic and reusable.
Wrap in IFERROR to show a custom message like "No match" instead of "Not found" when the lookup fails.
For exactly two criteria, consider V2_LOOKUP which has a simpler, more readable syntax.
The search range should include all columns you reference, including the return column and all criteria columns.
Sort your data by the most selective criterion first for faster mental scanning when reviewing the data.
The function works by accepting your primary search value, a data range, the column number to return, and then any number of additional value/column pairs. Each pair adds another matching criterion: the function will only return a result when ALL criteria match the same row. Matching is case-insensitive, so you do not need to worry about capitalization differences. If no row matches all criteria, the function returns "Not found".
This function is especially useful in business scenarios where data has composite keys. Instead of creating helper columns that concatenate values for lookup, you can match directly on the original columns. It works seamlessly with any data type including text, numbers, and dates. For simpler two-criteria lookups, consider V2_LOOKUP which provides a more streamlined syntax. Because it is included in the Free tier, every Unlimited Sheets user has access to this essential lookup upgrade from day one.
Häufige Fehler
Returns "Not found" unexpectedlyUrsache: One of the criteria values has trailing spaces, different formatting, or the column numbers are incorrect.
Lösung: Use TRIM() on your search values, verify column numbers match the correct columns in your range, and check for hidden characters.
Error: Missing required parametersUrsache: The searchValue, searchRange, or returnColumn argument was not provided.
Lösung: Ensure you pass at least three arguments: the value to search for, the data range, and the column number to return.
Returns wrong valueUrsache: The additional criteria arguments are not in proper value/column pairs, or the column numbering is off.
Lösung: Additional criteria must always come in pairs (value, columnNumber). Double-check that each column number is 1-indexed relative to the start of your search range.
Häufig Gestellte Fragen
The native VLOOKUP only matches a single value in the first column of your range. UNLIMITED_VLOOKUP lets you match on multiple columns at the same time by providing additional value/column pairs. This eliminates the need to create concatenated helper columns for multi-criteria lookups.
No, UNLIMITED_VLOOKUP performs case-insensitive matching. "Apple", "apple", and "APPLE" will all match each other. This applies to all criteria, not just the primary search value.
The function returns the text "Not found" when no row in the search range matches all specified criteria simultaneously. You can wrap the function in an IFERROR to display a custom message instead.
After the three required parameters, you can add pairs of arguments: a value to match and the column number to match it in. For example, adding "Red", 2 means "also match Red in column 2". You can add as many pairs as needed.
UNLIMITED_VLOOKUP returns only the first matching row it finds, scanning from top to bottom. If you need all matching rows, consider using FILTER or QUERY functions in combination.
Yes, you can use cell references for any parameter. For example, =UNLIMITED_VLOOKUP(B1, A1:D100, 4, C1, 2) will use the values in B1 and C1 as search criteria.
All column numbers are 1-indexed relative to the search range. Column 1 is the first column of your range, column 2 is the second, and so on. This applies to both the return column and the additional criteria columns.
Verwandte Funktionen
Beginnen Sie noch heute mit UNLIMITED_VLOOKUP
Installieren Sie Unlimited Sheets, um UNLIMITED_VLOOKUP und 41 weitere leistungsstarke Funktionen in Google Sheets zu erhalten.
