OmniDoc# which method is suitable for you?
Sort out the commonly used search functions in Excel, including VLOOKUP, HLOOKUP, INDEX and MATCH, LOOKUP and XLOOKUP, explain their respective search directions, sorting requirements and version restrictions, and help you choose the right function before reading the specific tutorial.
#What are the search functions in Excel? Comparison and selection of commonly used functions
Key points of this article
- The choice of search function depends on three things: which column the search value is in, which column the data to be returned is in, and whether the table will insert or delete columns.
- For daily exact matching, VLOOKUP is quick to use; when you need to search left or the column changes, INDEX combined with MATCH is more stable.
- XLOOKUP is more comprehensive and can directly specify the returned content when not found, but it is only available in newer Excel versions.
- LOOKUP and approximate matching rely on data arranged in ascending order. If used incorrectly, they will return seemingly normal but incorrect results. Sort and check before use.
- After the formula is written, use several known data samples to check the results, which is more reliable than just looking at whether there are any errors.
Many people who need to compare data between two tables for the first time, such as looking for names by job number or unit price by product number, will directly search for "Excel search function". More than a dozen functions often appear in the search results, each with a tutorial, but few people tell you what situations each of them is suitable for. The result is that I selected a usable function, but then an error occurred when the form was modified.
This article gives you a map for finding functions. After reading, you will know what problems the common VLOOKUP, HLOOKUP, INDEX and MATCH, LOOKUP, and XLOOKUP solve respectively, and make a choice before writing the formula. Specific syntax details and step-by-step tutorials can be viewed after selecting the function.
How to choose the search function? One sentence conclusion
Before choosing a function, answer three questions: which column of the table the search value is in, which column the result to be returned is in, and whether this table will insert or delete columns in the future. The table below can serve as a starting point.
| your situation | Recommended practices | reason |
|---|---|---|
| The search column is on the left, the required data is on the right, only check once | VLOOKUP, fourth parameter set to FALSE | Straightforward writing method and quick to get started |
| You need to search to the left, or table columns are frequently added or subtracted | INDEX WITH MATCH | You can also check the column on the left without relying on column numbers. |
| Data is arranged in rows | SEARCH | Symmetric with VLOOKUP, find by row |
| Using a newer version, I want to write clearly the display when not found. | XLOOKUP | The parameters are complete and you can specify the return content when it cannot be found. |
| The data has been sorted, and segmented values are obtained, such as tax rates or levels. | LOOKUP or approximate match | Get values by interval, but require data in ascending order |
If your table version is older and there is no clear left-right direction requirement, VLOOKUP and INDEX combined with MATCH are a safer starting point.
What are the different methods?
VLOOKUP: classic search from left to right
VLOOKUP searches for the lookup value in the column to the left of the range, and returns the contents of the specified column in the same row when found. Its parameters include search value, range, column number and matching method. Writing FALSE in the matching mode means exact matching, which is the setting required in most business scenarios.
It has two shortcomings. First, the search column must be located on the left side of the area, and the value cannot be obtained directly to the left. Second, the column serial number is a manually counted number. If a column is inserted in the middle, the column returned by the formula may be quietly misplaced, but an error may not necessarily be reported.
HLOOKUP: Find the corresponding version by row
The logic of HLOOKUP is the same as that of VLOOKUP, except that the search direction becomes horizontal, searching in the top row of the area, and returning the content of the specified row. It is more suitable when the data is arranged horizontally by month, project, etc. When tables are mostly arranged vertically, it is usually more intuitive to use VLOOKUP.
INDEX and MATCH: more flexible combination
INDEX is responsible for "returning the value at which position", and MATCH is responsible for "finding which row the search value is in". After combining the two, first use MATCH to calculate the line number, and then use INDEX to retrieve the corresponding content. The return column and the search column are specified separately, so you can search left and inserting new columns will not affect the results.
The trade-off is that the formula is longer than VLOOKUP, and beginners need to understand the relationship between the two steps. For tables that are maintained frequently and whose structure changes, this investment is usually worth it.
LOOKUP: Fit interval and sorted data
There are two common uses for LOOKUP. One is to search for large items that do not exceed the search value in the data arranged in ascending order, which is suitable for scenarios such as grade levels and stepped tax rates that take values based on intervals. The other is a vector form, which can correspond to values between two columns.
Its key requirement is that the data must be in ascending order. If the data is not sorted, the formula will not report an error, but the returned results may be wrong. Before use, you should confirm the sorting and check a few rows of results.
XLOOKUP: a new function with more complete functions
XLOOKUP directly specifies the search value, search range and return range. It does not require column numbers and can also search to the left. It has a very practical parameter: the content returned when it cannot be found can be written directly in the formula, such as "not found", so that there is no need to nest other functions to handle errors.
The limitation of XLOOKUP is version. It is only available in newer Excel versions. If colleagues open the file using an older version, the related formulas will not be calculated properly. Before sharing a form, confirm the other party’s version.
📊 Core comparison
| Contrast Dimensions | VLOOKUP | INDEX + MATCH | LOOKUP | XLOOKUP |
|---|---|---|---|---|
| Find directions | right only | From left to right | Usually to the right or by interval | From left to right |
| Requirements for sorting | No sorting is required for exact matching | No sorting is required for exact matching | Approximate matching requires ascending order | No sorting is required for exact matching |
| Whether it is stable after inserting the column | The column serial number needs to be adjusted manually. | More stable | Depends on the specific way of writing | More stable |
| What to do when you can't find it. | Need to cooperate with other functions | Need to cooperate with other functions | We should pay attention to whether the result is correct. | Parameter is specified directly in the |
| Version requirement | General support | General support | General support | Newer version required |
if you only occasionally match the data of two tables
VLOOKUP is enough to write an exact match. Make sure that the lookup column is on the left, and you can get the results quickly.
if your form often adds or deletes columns
Priority is given to INDEX and MATCH. The return column and the lookup column are independent of each other, and the formula is less prone to error after structural adjustment.
if you need to look left
Use INDEX with MATCH directly, or use XLOOKUP in newer versions. VLOOKUP cannot do this kind of search directly.
if you want to take values by grade or tax rate, etc.
Use LOOKUP or approximate matching, but first sort out the boundary values of the interval in ascending order.
if you share a file with others, the other person's version is uncertain
Avoid using XLOOKUP and use VLOOKUP or INDEX with MATCH for better compatibility.
# how to do it in OmniDoc
If you want to match the data hidden in the PDF report, you can first convert it into a table, and then write the search formula in Excel. The following steps are appropriate for this situation:
- Open the OmniDoc desktop client (download address: download.omnidoc.top) and load the PDF report.
two. If the report is scanned, first use the OCR function to identify the text, and then check whether the identified numbers and numbers are correct.
- Use the export feature of the PDF editor to export the content to an Excel file.
- In Excel, according to the previous comparison, choose VLOOKUP, INDEX with MATCH or XLOOKUP to write the formula.
- Check the results with several known data, make sure they are correct, and then use them in the complete form.
# frequently asked questions (FAQ)
1. What is the reason why VLOOKUP returns an error value?
There are three common reasons: there are spaces before and after the lookup value, the data format on both sides is inconsistent (text and numbers are mixed), or the lookup column is not on the left side of the area. Check these three points item by item before considering the formula itself.
2. Does the lookup function have to sort the data?
Exact matches usually do not require sorting. Only an approximate match of LOOKUP or VLOOKUP requires the search column to be sorted in ascending order.
3. Why is the same search, other people's results different from mine?
In most cases, there are duplicate values in the table, or the start and end rows of the lookup area are not aligned. The function only returns the item found first, and the duplicated data should be sorted out first.
4. Which is more appropriate to find a function or a PivotTable report?
If you just take a value or add a list of information, the lookup function is more direct. PivotTable is more appropriate when you want to summarize statistics by multiple dimensions.
# Summary
The search function of Excel has its own applicable scenarios. When choosing, you first look at the search direction, table structure and version, and then decide which one to use. VLOOKUP is suitable for simple right search, INDEX with MATCH is more flexible, and XLOOKUP is more complete but requires a newer version. After writing the formula, check it with the known data, and the result will be reliable.
