OmniDocHow to use the Excel LOOKUP function: vector and array search
Explain clearly the vector form and array form of the Excel LOOKUP function, focus on the approximate matching rules, sorting requirements and common errors, and give two reproducible examples of grade level and precise search.
Key points of this article
- LOOKUP only performs approximate matching: finding a large value that is no larger than the search value in ascending order data, suitable for taking values based on intervals, such as scores corresponding to levels.
- The search area must be sorted in ascending order, otherwise the results will be unpredictable, which is a common cause of LOOKUP errors.
- The result area in vector form can be placed on either side of the search area and can be searched to the left, which is something VLOOKUP cannot do.
- For precise search, you can use LOOKUP(2,1/(condition),result), which returns the last result that satisfies the condition.
- #N/A is returned when the search value is smaller than the minimum value of the range. After exporting the table or organizing data from PDF, you should first check the sorting and data type.
When searching in Excel, many people first think of VLOOKUP or INDEX plus MATCH, but there is a type of problem where LOOKUP is more direct: classifying levels by score, applying discount brackets by amount, and finding the stage to which they belong by date. What these scenarios have in common is that they are not looking for exactly the same value, but which interval it falls within. Most readers who search for "excel lookup function lookup" want to find out what this function is doing.
LOOKUP can be written in two ways: vector form and array form. Both default to approximate searches, requiring data to be sorted in ascending order. If you don't understand this premise, even if the formula does not report an error, the result may be wrong. This article only talks about the rules of LOOKUP itself, and does not repeat the precise search tutorials of VLOOKUP and INDEX/MATCH.
How to use LOOKUP? One sentence conclusion
The core of LOOKUP is to find a large value that is not greater than the search value in the data arranged in ascending order, and then return the result at the corresponding position. Use it when you need to obtain values based on intervals. When strict matching is required, give priority to INDEX plus MATCH, or use a special exact search writing method in LOOKUP.
| your situation | Recommended practices | reason |
|---|---|---|
| Divide the gears according to points, amount, and number of days | Vector form, search area arranged in ascending order | Approximate matching exactly corresponds to "which bracket it falls on" |
| The results are listed to the left of the search column | In vector form, specify the search area and result area respectively. | LOOKUP is not restricted by left and right directions |
| Need to find the last occurrence of the same value | LOOKUP(2,1/(condition),result) | Exploiting the feature that error values are ignored to return the last match |
| The data is a two-dimensional table, and I only want to get the last row or column. | Array form | The function automatically determines the search direction |
| The data is not sorted and exact matching is required | Instead of using ordinary LOOKUP, use INDEX plus MATCH precise mode. | Unsorted approximate lookup results are unreliable |
Two ways to write LOOKUP
Vector form: specify the search area and result area
The syntax of the vector form is LOOKUP(lookup value, lookup range, result range). The search area is a column or a row, the result area is the same size as it, and the positions correspond to each other.
Take grade levels, for example. Suppose the lower limits of scores are written in column A as 0, 60, 70, 80, and 90, and the corresponding grades as "failed", "passed", "medium", "good" and "excellent" are written in column B. To determine which category 75 points belongs to, the formula is LOOKUP(75, column A area, column B area), and the result is "medium". Because 75 is not less than 70 but less than 80, the lower limit of which is not greater than 75 is 70.
An important feature of the vector form is that the result area can be placed on the left or right side of the search area, and the two are not limited to each other. If the rank column is to the left of the score column, it can still be used directly without adjusting the column order like VLOOKUP.
Array form: only give one area
The syntax of the array form is LOOKUP(lookup value, array). The function determines the search direction based on the shape of the array: if there are more columns than rows, it searches in the first row and returns the value at the corresponding position in the last row; if the number of rows is not less than the number of columns, it searches in the first column and returns the value at the corresponding position in the last column.
This method of writing is suitable for two-dimensional tables with simple structure. But its result depends on the shape of the array, and if the table changes slightly, the search direction may change, so in scenarios where columns need to be specified explicitly, the vector form is more secure.
how does approximate matching work
LOOKUP adopts the idea of dichotomy when searching, it does not compare whether each item is completely equal one by one, but assumes that the data has been arranged in ascending order, gradually narrows the scope, and finds the big item that is not larger than the search value.
Here are three rules to remember:
-when the lookup value is greater than or equal to an item in the zone, the corresponding result of that item is returned until a larger item is encountered. -when the lookup value is less than the small value in the zone, there are no items to match, and the result is # NambiA. -when there are duplicate values in the data, approximate matching usually returns the result that is lower in the same value.
Text can also be looked up approximately, and the comparison rules are alphabetical and case-insensitive.
Common misunderstandings
The first misunderstanding is that "LOOKUP sorts automatically". It won't. If the regions are not sorted in ascending order, the function still returns a number, but it probably doesn't find the line you expect. When the result is obviously unreasonable, check the sort first, and then check the formula.
The second misconception is that "LOOKUP can also do a precise search." You can't write it in ordinary way. The last parameter of VLOOKUP is set to FALSE to match exactly, and LOOKUP does not have such a switch. If you want to find it exactly, you need to use the 1 / (conditional) method described later, or use INDEX plus MATCH instead.
The third misunderstanding is that "LOOKUP supports wildcards". This is not true at all. In LOOKUP, asterisks and question marks are just ordinary characters and cannot be used to blur matches as COUNTIF does.
# Core comparison
The following table compares the differences between LOOKUP and common search methods in terms of judgment basis and usage restrictions.
| Contrast item | LOOKUP vector form | LOOKUP array form | VLOOKUP approximate matching | INDEX plus MATCH exact match |
|---|---|---|---|---|
| Matching mode | Approximate, take a large value not greater than the look-up value | Approximate, determine direction according to array shape | Approximate, need to be omitted or set to TRUE | Precise. You need to specify a matching type of 0. |
| Sorting requirement | Must be ascended | Must be ascended | The first column must be in ascending order | Sorting is not required |
| Search direction | The result area can be on either side | Determined by the shape of the array | Values can only be taken from left to right | The result area can be on either side |
| Suitable for the scene | Interval value and grade division | A simple two-dimensional table takes the last row or column | Interval query of ordered tables | General precise search |
# how to use it
Here is an example of "percentage by sales" to illustrate the basic steps of using LOOKUP in Excel.
- List the lower limits of the gear in an area, such as 0, 10000, 30000, 50000, and sort them in ascending order.
two. Write out the commission ratio corresponding to each gear in the adjacent column, which corresponds to the lower limit one by one.
- Enter LOOKUP (sales cell, lower limit area, scale area) in the cell to be calculated.
- Test with several boundary values, such as 9999, 10000, 30000, to make sure it is in the correct gear.
- If # NCMA is returned, check to see if the sales are less than the small lower limit, or if the lower limit area is mixed with text or spaces.
If your data is originally a report in PDF format, you can first use the export function in the desktop client's PDF editor to export the table to an Excel file, and then use the above steps in the table. After export, be sure to check that the number is recognized as text, because the number in the text format can cause the comparison result to be abnormal. For the scanned version of PDF, you can first use the OCR function to identify the text, and then check whether the numbers are accurate, and important data need to be checked manually.
# which method is suitable for you?
If your table has been arranged by interval, the goal is to take out the corresponding value of the gear. The form of the LOOKUP vector is concise enough that no additional auxiliary columns are needed.
If your data is cluttered, unsorted, or needs to match numbers and names exactly, it is recommended to use INDEX plus MATCH, which does not rely on sorting and is easier to understand and maintain. The newer version of Excel also provides functions such as XLOOKUP, which can meet the needs of accurate and approximate matching at the same time, so it is suitable to be used in the new table, but you need to pay attention to whether the objects sharing the file use the same version.
# frequently asked questions (FAQ)
1. LOOKUP returned # NCMA. Is the formula written wrong?
Not necessarily. A common reason is that the lookup value is less than the small value of the lookup area, for example, the lower limit starts at 60 but looks for 55. It may also be that numbers are stored as text, or that the range contains invisible spaces. Check these two points first, and then check the sorting.
2. What is the difference between approximate matching of LOOKUP and VLOOKUP?
Both are ordered, but the search value of VLOOKUP must be in the first column of the table, and the result can only be on the right; the search area and result area of LOOKUP can be specified separately, and the location of the result is not restricted. LOOKUP is more convenient when you need to look to the left.
3. The data is not sorted, can you use LOOKUP to do accurate search?
Ordinary LOOKUP is not suitable. You can use the word LOOKUP (2 search area = search value), which returns the last result that meets the criteria and does not depend on sorting. If you need the first match, use INDEX plus MATCH instead.
4. Why does the same value appear twice, only to take the latter one?
Approximate matching usually stops at the back of the item when it encounters duplicate values, which is related to the characteristics of binary search. If you need to make a clear distinction, you should add a number to the data that distinguishes each row, or use a precise search instead.
5. Can the data generated by machine translation or OCR be directly used in LOOKUP?
Yes, but check it first. Both machine translation and OCR may identify or mistranslate numeric and hierarchical names, resulting in a search area that is not as expected. It is recommended that before the formal calculation, spot check several boundary values and confirm the stall name item by item.
# Summary
The value of LOOKUP lies in taking values according to intervals. It relies on ascending data and returns large results that are not greater than the search value. By mastering the rules of vector form, array form, and approximate matching, you can determine when to use it and when to switch to precise search. Check sorting and data types before using them to avoid most errors.
