V2_LOOKUP
Two-criteria VLOOKUP with a simplified, readable syntax.
=V2_LOOKUP(value1, value2, searchRange, column1, column2, returnColumn)Gibt zurück: any
Übersicht
V2_LOOKUP is a streamlined lookup function designed for the most common multi-criteria scenario: matching exactly two values across two columns. While UNLIMITED_VLOOKUP supports any number of criteria, V2_LOOKUP provides a cleaner, more intuitive syntax when you know you need exactly two matching conditions.
Parameter
| Parameter | Typ | Erforderlich | Beschreibung |
|---|---|---|---|
value1 | any | Ja | The first value to match. |
value2 | any | Ja | The second value to match. |
searchRange | Range | Ja | The data range to search through (e.g., A1:E50). |
column1 | number | Ja | The column number (1-indexed) to match value1 against. |
column2 | number | Ja | The column number (1-indexed) to match value2 against. |
returnColumn | number | Ja | The column number (1-indexed) from which to return the result. |
Beispiele
Find salary by name and department
Look up John in the Sales department and return his salary from column 3.
=V2_LOOKUP("John", "Sales", A1:C10, 1, 2, 3)Eingabe
| Name | Department | Salary |
| John | Engineering | $95,000 |
| John | Sales | $78,000 |
| Sara | Sales | $82,000 |
Ausgabe
$78,000Quarterly revenue by region
Find the North region's Q2 revenue from a quarterly breakdown table.
=V2_LOOKUP("North", "Q2", A1:D6, 1, 2, 4)Eingabe
| Region | Quarter | Units | Revenue |
| North | Q1 | 450 | $135,000 |
| North | Q2 | 520 | $156,000 |
| South | Q2 | 380 | $114,000 |
Ausgabe
$156,000Product price by name and size
Find the price of a large coffee from a product/size pricing matrix.
=V2_LOOKUP("Coffee", "Large", A1:D8, 1, 2, 4)Eingabe
| Product | Size | Calories | Price |
| Coffee | Small | 5 | $2.50 |
| Coffee | Large | 10 | $4.50 |
| Tea | Large | 0 | $3.50 |
Ausgabe
$4.50Student course grade retrieval
Look up Maria's grade in Biology 101 by matching student name and course name.
=V2_LOOKUP("Maria", "Biology 101", A1:D20, 1, 2, 4)Eingabe
| Student | Course | Credits | Grade |
| Maria | Chemistry 201 | 4 | A |
| Maria | Biology 101 | 3 | B+ |
| Alex | Biology 101 | 3 | A- |
Ausgabe
B+Shipping rate by origin and destination
Look up the shipping cost for US-to-EU shipments from a rate table.
=V2_LOOKUP("US", "EU", A1:D10, 1, 2, 4)Eingabe
| Origin | Destination | Days | Cost |
| US | US | 3 | $5.99 |
| US | EU | 7 | $19.99 |
| EU | US | 8 | $22.99 |
Ausgabe
$19.99Anwendungsfälle
Regional sales reporting
Pull quarterly revenue figures by matching region and quarter in consolidated sales reports.
Menu pricing
Look up item prices by matching both the menu item name and the size or variation.
Course scheduling
Find classroom assignments by matching course name and time slot in a scheduling spreadsheet.
Insurance rate lookup
Retrieve premium rates by matching both coverage type and risk category from a rate table.
Warehouse location mapping
Find bin locations by matching product SKU and warehouse zone in an inventory map.
Patient records
Look up treatment details by matching patient ID and visit date across appointment records.
Profi-Tipps
Use V2_LOOKUP for cleaner formulas when you always have exactly two criteria, and switch to UNLIMITED_VLOOKUP only when you need three or more.
The column numbers are 1-indexed relative to the search range start, not the sheet. If your range is C1:G10, column 1 refers to column C.
Combine with IFERROR for user-friendly error messages: =IFERROR(V2_LOOKUP(...), "No match found").
You can reference cells for value1 and value2 to create dynamic lookups that update when input cells change.
The function takes two search values, a data range, the column numbers where each value should be found, and the column number from which to return the result. It scans each row and returns the value from the return column of the first row where both criteria match simultaneously. Like UNLIMITED_VLOOKUP, all matching is case-insensitive.
V2_LOOKUP is particularly useful when working with datasets that have natural two-part keys, such as first name and last name, product and size, region and quarter, or date and category. Instead of building complex INDEX/MATCH formulas or creating concatenated helper columns, you can write a single, readable formula.
The function returns "Not found" if no row matches both criteria. You can handle this gracefully with IFERROR. For lookups requiring more than two criteria, switch to UNLIMITED_VLOOKUP which accepts unlimited value/column pairs. For checking if a value merely exists without returning data, see EXISTS_IN_COLUMN.
Häufige Fehler
Returns "Not found" for data that existsUrsache: The column numbers do not correspond to the correct columns in the range, or extra whitespace exists in the data.
Lösung: Verify that column1 and column2 reference the correct positions within the range. Apply TRIM() to values if your data may have leading or trailing spaces.
Error: Missing required parametersUrsache: One or more of the six required parameters was omitted.
Lösung: V2_LOOKUP requires exactly six arguments: value1, value2, searchRange, column1, column2, and returnColumn. Ensure all are provided.
Error: Invalid search rangeUrsache: The searchRange parameter is not a valid range reference or is empty.
Lösung: Pass a proper range reference like A1:E50. Make sure the range contains data and is formatted as a standard Google Sheets range.
Häufig Gestellte Fragen
Use V2_LOOKUP when you have exactly two matching criteria. Its syntax is more explicit and readable because each parameter has a clear role, unlike UNLIMITED_VLOOKUP where additional criteria are variadic value/column pairs.
Yes. The column1 and column2 parameters let you specify any columns within the search range. They do not need to be adjacent or in any particular order.
No, all comparisons are case-insensitive. "sales" and "Sales" will match each other. This applies to both value1 and value2.
V2_LOOKUP returns the value from the first matching row found (scanning top to bottom). If you need all matches, use Google Sheets' FILTER function instead.
Yes, V2_LOOKUP converts values to strings for comparison, so numbers and dates work. However, be aware that date formatting may affect matching. For dates, ensure consistent formatting in your data.
The function returns the text string "Not found" when no row in the search range matches both value1 in column1 AND value2 in column2 simultaneously. Check your data for typos or formatting differences.
Verwandte Funktionen
Beginnen Sie noch heute mit V2_LOOKUP
Installieren Sie Unlimited Sheets, um V2_LOOKUP und 41 weitere leistungsstarke Funktionen in Google Sheets zu erhalten.
