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.

LOOKUP function searches in Excel Picture 1

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.

LOOKUP function searches in Excel Picture 2

2. Array reference to array.

LOOKUP function searches in Excel Picture 3

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

LOOKUP function searches in Excel Picture 4

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!