Excel provides a versatile function called VLOOKUP that searches the first column of a search range.
Excel VLOOKUP – targeted use, clean results
VLOOKUP in Excel is one of the most frequently used functions when individual values need to be extracted from large tables. To ensure that this works reliably, you need more than just the right formula – the structure of the data and a keen eye for sources of error are also important.
- The matrix must start on the left: VLOOKUP only searches the first column of the selected range. For example, if you have the customer number in column D, you must also start the search range there – i.e., not “A:F,” but “D:F.” The first column is always used as the basis for comparison. If you ignore this, you will get incorrect results or no results at all.
- Work more reliably with table objects: As soon as you format your data as a “table” (keyboard shortcut: Ctrl + T), Excel automatically responds to subsequent additions. The VLOOKUP formula remains valid even if you add new rows. The formula =VLOOKUP(A2;Table1;3;FALSE) then accesses the updated data correctly – ideal for growing lists.
- FALSE instead of TRUE – control the search behavior: Without an exact match, you risk inaccurate results. The fourth digit of the formula – the so-called range_reference – should almost always be set to FALSE. Only then will Excel check for an exact match. TRUE is only suitable for sorted data – which is rarely guaranteed in everyday use.
- Check and standardize data formats: Numbers in text format or numbers in number format – both look the same, but behave differently. If VLOOKUP does not return any results even though the value is in the list, there is almost always a format conflict. With =ISTEXT(A2) or =ISNUMBER(A2), you can quickly identify where the problem lies. Tools such as =GLÄTTEN() or =WERT() automatically clean up the mess.
- Use absolute and relative cell references selectively: When you copy formulas, cell references are also copied. This can have disastrous consequences for matrix data. Therefore, use absolute cell references with $ (e.g., $A$2:$D$100) to reliably drag formulas from top to bottom or across the table without the range “shifting.”
- Catch errors elegantly: No one wants to see “NV” in form letters or dashboards. Combine VLOOKUP with IFERROR to generate an alternative output: =IFERROR(VLOOKUP(A2, Table1, 3, FALSE), “Not found”)
- This is not cosmetic, but functional – for example, for exports or automated data processes.
- Know the limitations of VLOOKUP: As soon as columns are moved or the structure of the table changes, VLOOKUP returns incorrect results. Reverse searches – i.e., from right to left – are also not possible. In such cases, XVERWEIS or INDEX/VERGLEICH are recommended.
XVERWEIS, INDEX & Co. – Alternatives with more control
Since Microsoft 365 and Excel 2021, XVERWEIS has been available as a more robust alternative that overcomes many of the weaknesses of SVERWEIS. The combination INDEX/VERGLEICH is also considered flexible, especially when Excel versions or requirements change.
- XVERWEIS: Search backwards without detours: The biggest difference: XVERWEIS also allows returns from columns to the left of the search term. Example: =XVERWEIS(B2;Table1[Name];Table1[Customer Number];“No match”). This allows you to search tables whose structure is not optimally designed. VLOOKUP would fail here.
- Column index omitted – less prone to errors: With XVERWEIS, you specify two specific cell ranges: the search range and the return range. You don’t have to think about counting as you do with VLOOKUP (3rd column, 4th column, etc.)This reduces errors and improves clarity.
- Control error messages: The fourth argument “if_not_found” in XVERWEIS replaces the detour via IFERROR. Easy to read, fewer typos, direct access – the function responds more efficiently to empty cells or incorrect values.
- Control search direction and comparison behavior: XVERWEIS offers two additional parameters: “Comparison mode” (0, -1, 1) and “Search mode” (1 from top, -1 from bottom). This allows you to search specifically for price scales or duplicate entries, for example – something that is impossible with VLOOKUP.
- XVERWEIS on mobile devices and on the web: XVERWEIS works in Microsoft Excel Mobile, but complex dynamic formulas are limited. In Excel Online, both SVERWEIS and XVERWEIS run stably as long as the cell formats are correct. On mobile devices, it is recommended to use simple structures, as some controls are missing.
- INDEX/MATCH: Versatile and version-independent: INDEX retrieves the value, MATCH finds the right location. Formula example: =INDEX(D2:D100;MATCH(A2;B2:B100;0))
Unlike VLOOKUP, this combination does not break when you rearrange columns. It is suitable for complex calculations or when XLOOKUP is not available (e.g., Excel 2016).
Performance with large amounts of data: If you work with thousands of data records, you will achieve your goal faster with INDEX/MATCH or XLOOKUP than with VLOOKUP. Both process large amounts of data more efficiently, especially in dynamic dashboards or when exporting from third-party systems.
Practical example – VLOOKUP step by step
Many people use Excel every day, but VLOOKUP often causes confusion – especially if you don’t work with formulas on a regular basis. The following example shows how you can create a working VLOOKUP with a clean structure and just a few steps. The goal is to automatically retrieve the corresponding phone number from a customer list.
- Structure the source table: Create a table with three columns: “Customer number,” “Name,” and “Phone number.” Each row contains exactly one data record. Important: The column with the value you are looking for – in this case, the customer number – is on the far left. VLOOKUP always compares only with the first column in the search range. Example: “1002 Langer 0157 9876543”
- Set up a second table for the query: Further to the right – or in another table sheet – create a small list with two columns: “Customer number” and “Phone number.” In the “Customer number” column, enter the value you want to search for – for example, “1002”. The second column remains empty for now and will be filled automatically later.
- Set up the VLOOKUP correctly: Click in the cell where you want the result to appear – for example, cell F2. Now enter the following formula: =VLOOKUP(E2,A2:C100,3,FALSE)
- This formula means: Search for the value from cell E2 (in this case “1002”) in the first column of the range A2 to C100. Then return the value from the third column (C) of the same row. The “FALSE” at the end ensures that only exact matches are accepted.
- Test the formula and check the result: As soon as you press Enter, Excel automatically displays “0157 9876543” in cell F2 – the phone number of the customer you were looking for. If you change the value in E2 (for example, to “1003”), the result in F2 also changes automatically. The formula adapts dynamically to the input value.
- Avoid typical sources of error: Check whether numbers in the search area are actually formatted as numbers. The reason for a “NV” error is often that the value in E2 is formatted as a number, but as text in the source table. This is hardly noticeable visually, but has technical consequences. You can check this with “=ISTEXT(A2)”. If necessary, “=VALUE()” can be used to correct the cell content.
- Fix cell references when multiple rows are used: Drag the formula down to query additional customers and fix the range with dollar signs: =VLOOKUP(E2;$A$2:$C$100;3;FALSE)
This keeps the search range unchanged even if you copy or drag the formula down.
Intercept error messages, improve output
To display a comprehensible message such as “Customer not found” instead of “NV”, add the following to the formula: =IFERROR(VLOOKUP(E2;$A$2:$C$100;3;FALSE); “Customer not found”)
This makes the table look neater and allows the user to see immediately when there is no result.
