Excel Lookup function (Vector and Array, 5 examples, Approximate matching)
There are two forms for the lookup function in Excel, one in the form of a Vector and the other in the form of an Array, where there are three parameters for the Vector form and there are two parameters for the Array form, that is, the Array form omits the result_vector.
Chapters:
0:00 Excel Lookup function
0:13 Syntax and Explanation for Vector form
0:39 Examples of the Lookup function and the principle
1:21 Explanation of the principle of searching
2:28 Example two: Find Approximation (Excel fuzzy Lookup)
3:08 Example three: Finds a value that is smaller than the smallest in the search range
3:54 Syntax and Explanation for Array form
4:31 Examples of Array form
5:17 Example two: Find Approximation
6:01 Difference between Lookup and Vlookup in Excel
Description:
Excel Lookup function (Vector and Array, 5 examples, Approximate matching)
One. How to use Lookup function in the form of a Vector
The vector form of Lookup looks up a value in a row or column and returns the value of the corresponding position in another row or column.
Syntax
=LOOKUP(lookup_value, lookup_vector, [result_vector])
Explanation
1. lookup_value is required and a value found, which can be a reference, a number, text, a name, or a logical value.
2. lookup_vector is required and a range found, it can only be a row or a column. The value of the search range must be in ascending order, otherwise it may return an incorrect result. It can be a cell reference, number, text, name or logical value, the text is not case-sensitive.
3. [result_vector] is the range in which the result is returned , which is optional (can be filled in or not filled); it can only be a row or a column, and the size should be the same as the search range; if the [result_vector] is a cell (such as A2 or A2:A2), it is a row (that is, horizontal) by default, which is equivalent to A2:B2.
Note:
A. If the lookup_value cannot be found, the lookup function will return the maximum value less than or equal to the lookup_value.
B. If the lookup_value is less than the minimum value of the lookup_vector, the lookup function will return a #N/A error.
Examples of the Lookup function and the principle
Example one: Find a $9 apparel from the Clothing sales table
1. Select C2:C8, select the "Data" tab, click the "AZ" icon, pop up the "Sort Warning" window, select "Expand the selection", click "OK", and arrange the "Price" column in ascending order, copy and paste the formula =LOOKUP(A11,C2:C8,A2:A8) to cell B11, press Enter in the keyboard, then return "Pink short sleeve shirt".
2. Formula explanation:
A11 is the lookup_value, C2:C8 is the lookup_vector, A2:A8 is the result_vector, and both the lookup_vector and result_vector are columns, and the lookup_vector is arranged in ascending order.
Example two: Find Approximation (Excel fuzzy Lookup, Fuzzy match in Exce, Find values that are not present in the search range)
Suppose we want to find clothing that costs $10. Copy and paste the formula =LOOKUP(A11,C2:C8,A2:A8) to cell B11, press Enter in the keyboard, then it returns "Pink short sleeve shirt".
Example three: Finds a value that is smaller than the smallest in the search range
Suppose we want to find clothing that costs $5. Also copy and paste the formula =LOOKUP(A11,C2:C8,A2:A8) to cell B11 and press Enter in the keyboard, then a #N/A error is returned.
Two. How to use lookup function in Array form
Syntax
=LOOKUP(lookup_value, array)
Explanation
1. lookup_value is a required value to be found, which can be a reference, number, text, name, or logical value of a cell.
2. array is an array, which is required, it is a collection of values in rows and columns, the values of the array must be arranged in ascending order, otherwise it will return an error result, it can be a reference to a cell, a number, text, a name or a logical value, the text is not case-sensitive.
(3) Examples of Array form
Example one
Suppose we want to find clothing numbered WS-581 in the A2:D8 of the clothing sales table. Select A2:A8, select the "Data" tab, click the "AZ" icon to sort all garments in ascending order, enter WS-581 in cell A11, continue to enter the formula =LOOKUP(A11,A2:D8) in cell B11, press Enter in the keyboard, and return 28, which is exactly the price of garments numbered WS-581.
Example two: Approximate search (Excel fuzzy Lookup, Fuzzy match in Excel)
Suppose we want to find clothing numbered S-36 in the Clothing Sales table. Enter the number S-36 in cell A11, then enter the formula =LOOKUP(A11,A2:D8) in cell B11, press Enter in the keyboard, then 28 is returned.
Three. Difference between Lookup and Vlookup in Excel (Excel Lookup vs Vlookup function)
The Lookup function is equivalent to the approximate matching of the Vlookup function, which is mainly used to find a row or a column, and Vlookup can be used to find a row and a column, or can be used to find multiple rows and columns, and the function is much more powerful than Lookup.
Смотрите видео Excel Lookup function (Vector and Array, 5 examples, Approximate matching) онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Loinsure 10 Декабрь 2023, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 135 раз и оно понравилось 0 людям.