LOOKUP function searches in Excel
The LOOKUP () function is a search function in Excel, the function is more advanced than the two search functions VLOOKUP () and HLOOKUP ().
The function can distinguish the search area as a row or a column to handle it best.
The following article describes the details of the LOOKUP () function in Excel.
Description
The LOOKUP () function returns a value from a data range consisting of 1 column or 1 row, or from an array.
The LOOKUP () function is an improvement of the two functions VLOOKUP () and HLOOKUP () because it has the additional function to distinguish the search area as rows or columns.
The LOOKUP () function has two forms:
- Array form: the function finds the value specified in the first column or row of the array and then returns the value at the same position in the last column or row of the array. An array should be used when the list contains few values and the value does not change.
- In Vector form: the function finds a value in the data area consisting of a row or a column and then returns a value of the same position in a second row or column. The vector type should be used when the lookup list contains many values or the values can change.
Note
- The data to be searched by the LOOKUP () function must be arranged in ascending order for the LOOKUP () function to work properly.
- You can use the LOOKUP () function instead of the IF () function.
NETWORK FORMAT
Syntax
= LOOKUP (lookup_value, array)
Inside:
- lookup_value: lookup_value value can be searched in an array, lookup_value can be number, text, logical value, name or reference to a value.
- array: The lookup field consists of cells containing text, numbers or logical values in which you want to lookup lookup_value in.
Note
- If lookup_value is not found in the array, the function will use the largest value that is less than or equal to lookup_value.
- If lookup_value is smaller than the smallest value in the first column or row of the array, the function will return an error.
- If array is an area with more columns than rows, LOOKUP () will find lookup_value in the first row. Conversely, if the array is square or has more rows than the number of columns, the function will lookup_value in the first column.
- The values in the array must be sorted in ascending order for the most accurate results.
- LOOKUP () function is not case sensitive.
For example
1. Enter the array directly into the array.
2. Array reference to array.
VECTOR FORM
Syntax
= LOOKUP (lookup_value, lookup_vector, [result_vector])
Inside:
- lookup_value: value to search. Lookup_value can be a number, text, logical value, name or reference to a value.
- lookup_vector: contains the search value, containing only one row or one column. Lookup_vector can be text, numbers or logical values.
- result_vector: the result value contains only one row or column and the result_vector must be the same size as lookup_vector.
Note
- Values in lookup_vector must be sorted in ascending order for the most accurate results.
- The function is not case sensitive.
- If lookup_value is not found in lookup_vector, the function will use the largest value in lookup_vector that is less than or equal to lookup_value.
- If the lookup_value value is smaller than the smallest value in lookup_vector, the function returns the error value.
For example
The above article describes in detail the forms of the LOOKUP () function in Excel, hopefully through the article you will understand and know how to use the LOOKUP () function. Good luck!
You should read it
- How to combine Vlookup function with Left function
- The Match function (the function searches for a specified value in an array or cell range) in Excel
- Basic Excel functions that anyone must know
- Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCH
- What is DNS and DNS Lookup?
- How to use Hlookup function on Excel
- How to use the SUM function to calculate totals in Excel
- How to use VLOOKUP Function in Excel
May be interested
- Function Address - The function returns the address of a cell in Excel (usage, examples, examples)the following article dexterity software will introduce readers to the address function one of the most popular reference and lookup functions in excel.
- DATE Function: Converts numbers to a valid date formatthe date function in excel is a useful and most commonly used function. let's learn how to use the date function in excel with tipsmake.com.com!
- How to use the LEN function in Excelthe len function in excel is used to measure the length of a certain string of characters, including spaces and correctly return the total character for the user.
- How to use COUNTIF function on Excelthe countif function on excel is the cell count function to satisfy the given condition, often used in statistics tables.
- How to use the MOD function and QUOTIENT function in Excelinjury in excel has many ways of doing it, can be used manually or using the calculation function.
- 8 little-known Excel functions that can save you a lot of workeven seasoned excel users often find themselves stuck performing tasks manually that could be automated with a few clever functions.
- How to use MID functions to get strings in Excelmid function in excel is a function that takes the middle character string corresponding to the value that the user requires to perform.
- How to use Excel's VALUE functionexcel's value function will convert a string to a digital form, which can be combined with other functions such as left function, right function, mid function.
- How to use the WRAPROWS function in Excelhaving trouble with handling large data sets in excel? then learn how to use the wraprows function to split them into more manageable rows.
- How to use the IFS function in Excel 2016the ifs function in excel is a logical function introduced in excel 2016. this function is a function that replaces the nested if function and is much easier to use. the ifs function checks one or more conditions and returns a value that meets the first true condition.