5 ways to use the LOOKUP function in Excel function learning

In the previous article "Excel Function Learning: Talking about Multiple Summation Functions", we learned several summation functions. Today we are going to talk about the LOOKUP function. When people used the VLOOKUP function in the past, they often encountered some troubles, that is, the VLOOKUP search only found the first value that met the conditions and then stopped searching, and it could only be found from left to left. Right search is simply too restrictive. Today I will introduce to you the LOOKUP function, which can perfectly solve these two problems. It is so useful!

#The LOOKUP function is very powerful and is known as the king of reference functions. To fully master it, you must understand its 5 common uses and its binary search principle. Since there are many relevant knowledge points, the tutorial will be divided into two parts. Today we will first learn about the 5 common uses of this function through five examples. Tomorrow we will talk about the principle of binary search and the two LOOKUP problems left in the previous article.

1. General citation

Format: LOOKUP (lookup value, lookup area)

Example 1: Search for Chinese language scores based on names, the formula is =LOOKUP(H2,C2:D19)

Example 2: Search for English scores based on names, The formula is: =LOOKUP(H2,C2:F19)

Through these two examples, we can find the structure of the formula when LOOKUP searches Very simple, The search value and the result you are looking for are located in the first and last columns of the search area respectively.

But just understanding this usage is not enough. If we try another data, we may find the problem:

When the search name becomes Zhao Yongfu, the result is wrong. This is because the LOOKUP function uses binary search, which is fuzzy matching. Regarding this, we will discuss this in tomorrow's tutorial "LOOKUP" Full explanation of function usage (Part 2) - LOOKUP's binary search principle" is explained in detail.

Therefore, when using LOOKUP for regular search, a very important step is to sort in ascending order according to the search content (column c where the name is located) .

After we sort, the result of the formula immediately becomes correct. Isn’t it amazing!

#This raises a new question. If the data cannot be sorted, can the LOOKUP function still be used?

It definitely works. Let’s take a look at the second usage of the LOOKUP function.

2. Accurate search routines

Format: =LOOKUP(1,0/(search range=search value), Result range)

Enter the formula in cell I2:

=LOOKUP(1,0/(C2:C19=H2),D2:D19) , press Enter to see the correct result.

What does 1 and 0/ in this routine mean? It is also one of the most frequently asked questions. Before explaining the principle of dichotomy, let’s briefly talk about the formula. the meaning of. 1 is the value to be found, but the conditions have changed. Instead of searching for the name directly, a set of logical values ​​are obtained based on the name:

Note that there is only one TRUE here, also That's the name we're looking for.

Next, divide these logical values ​​by 0. When calculating, the logical value TRUE represents 1, and FALSE represents 0. When the denominator is 0, which is FALSE, the calculation result is an error value:

Therefore, the job of LOOKUP becomes to find 1 in a set of data. Since this set of data has only one 0, and the others are all error values, dichotomy makes LOOKUP only find the last number that is not greater than the search value , so only 0 can be found, and finally based on the row position of 0 (No. Line 2) Get the data corresponding to the third parameter, that is, D2 is the result we need.

This part of the content is considered to be a more difficult knowledge point in function learning. Beginners may have difficulty understanding it. The content is easy to understand. If you don’t fully understand it now, just remember this routine: =LOOKUP(1,0/(search range=lookup value), result range). At the same time, this routine can also be extended to use multi-condition precise search:

=LOOKUP(1,0/((search range 1=search value 1)* (search range 2=search value 2)* (search range 3=search value 3)), result range)

is to find the value you are looking for in each search range. After multiplying the obtained logical value, the position that meets multiple conditions at the same time is 1. The principle and single The conditions are the same.

3. Reverse lookup routine

Different from the VLOOKUP we are familiar with, use the LOOKUP function It is very simple to perform a reverse lookup. The formula structure is:

=LOOKUP (lookup value, lookup column, result column). The following example is sorted by name and then reverse lookup. Effect:

If the data cannot be sorted, use the exact search routine: =LOOKUP(1,0/(C2:C19=H8),B2:B19)

4. Routines for searching by interval

Give corresponding comments based on the student’s total score. Scores below 50 are "very poor", scores between 50 and 100 are "poor", scores between 100 and 150 are "average", scores between 150 and 200 are "better", and scores between 200 and 250 are "excellent" , those with a score of 250 and above are considered "experts".

The formula used here is:

=LOOKUP(G2,{0,50,100,150,200,250;"Very bad","Poor"," Average", "Better", "Excellent", "Expert"})

is divided into six levels according to the requirements of the comments. If you use the if function to do it, it will be very verbose. Use LOOKUP to handle this Class questions are very convenient, and the formula structure is also very simple:

=LOOKUP (score value, {lower limit 1, lower limit 2...; comment 1, comment 2...})

Pay attention to two points when writing this formula:

• 1. The second parameter of LOOKUP uses a constant array. The curly brackets here are entered manually, and a Separate with a semicolon, and the left side is the lower limit of each level. For example, in the expression below 50 points, the lower limit is 0, the lower limit of 50-100 is 50, and so on. Each number is separated by a comma; the right side of the semicolon is the corresponding Comments should be in quotation marks and separated by commas (all symbols in the formula are in English);

• 2. The numerical range should follow the ascending order, otherwise the result will be wrong.

5. About the importance of data sorting

When we follow When searching for names by student ID, I found that there would be an error. Student IDs are also arranged in ascending order. How could it be wrong?

This is the most common mistake. The student numbers here are arranged in ascending order just as we feel. In fact, the effect of ascending order is like this:

When using LOOKUP, if you do not use the exact search routine, remember to sort to ensure the correctness of the formula results.

Summary

1. Today I shared a total of five LOOKUP usage routines, namely regular search, precise search, and multi-condition search. Reverse search and search by interval. Beginners can master these routines and learn to apply them to solve problems;

2. The search principle of the LOOKUP function is different from the VLOOKUP we have learned before. The search method of the VLOOKUP function It is called traversal method. It will stop searching when it finds the first value that satisfies the condition. The LOOKUP function uses the dichotomy principle to search. It will stop searching only when it finds the last value that satisfies the condition. This is in the function documentation. Also mentioned;

We can verify this through a simple test:

3. On the data It is important that the sources are in ascending order:

If you cannot sort, you must use this routine: =LOOKUP (1,0/(search range = search value), result range), which is also determined by the characteristics of the dichotomy;

4. The LOOKUP function is very powerful, but it is also difficult to understand. If you want to completely understand this function, you must understand the principle of dichotomy. In tomorrow's tutorial, we will talk about what the dichotomy principle is, and by the way, we will explain the two problems left over from some time ago (LOOKUP solves the problem of rounding and the problem of data extraction).

Related learning recommendations: excel tutorial

The above is the detailed content of 5 ways to use the LOOKUP function in Excel function learning. For more information, please follow other related articles on the PHP Chinese website!