How to use the LOOKUP function in excel?

To search for a certain value by row or by column in excel, we immediately think of using VLOOKUP or HLOOKUP. With the following article, we will learn more about how to use the new function, LOOKUP. The LOOKUP function is more advanced than the HLOOKUP and VLOOKUP functions. The LOOKUP function can be used when you need to look at a row or a column and find a value from the same position in the second row or column.

Lookup function has 2 forms: array form and vector form.

1. LOOKUP function in array form

Syntax: = LOOKUP (lookup_value, array).

Inside:

- lookup_value: The value to search.

- array: The data area containing the value to search.

Attention:

- Where the search value is not found in the array -> the function returns the largest value in the array but is smaller than the value to be searched.

- If look_value is smaller than the smallest value in array -> Lookup function returns error value.

- The values ​​in the array must be sorted in ascending order for accurate results.

- If array has more columns than rows -> function finds in the first row.If the array has more squares or rows than columns, the function will look in the first column.

For example:

Sales of employees with a separate bonus.Find reward levels for employees who sell 30 products and employees who sell 55 products.

How to use the LOOKUP function in excel? Picture 1

Calculate the sales of employees who sold 30 products.In a cell to calculate enter the formula: = LOOKUP (30, D6: D9).

How to use the LOOKUP function in excel? Picture 2

Pressing Enter results the employee receives a reward of: 300,000

How to use the LOOKUP function in excel? Picture 3

- For employees with sales of 55 products not included in the bonus column but still worth the return.

In the cell to calculate enter the formula: = LOOKUP (55, D5: E9).

How to use the LOOKUP function in excel? Picture 4

Even salespeople who sold sales 55 received only 500,000 bonus

How to use the LOOKUP function in excel? Picture 5

2. LOOKUP function in vector form

Syntax: = LOOKUP (lookup_value, lookup_vector, [result_vector]).

Inside:

- lookup_value: The value to search, is a required parameter.

- lookup_vector: The value container to search for but only contains one row or one column, the region data can be numeric, text or logical values, which are required parameters.

- result_vector: The search results container contains at most 1 row or 1 column, which is an arbitrary parameter.

Attention:

- Values ​​in Lookup_vector must be arranged in ascending order so that the search process does not occur.

- Where the search value is not in the search area, the function takes the largest value in lookup_vector which is smaller than the value to be searched.

- If the search value is smaller than the smallest value in lookup_vector -> the function returns the error value.

For example:

You have an amount of money you want to buy a phone, you want to determine with which amount to buy the phone is most reasonable.

How to use the LOOKUP function in excel? Picture 6

For example, with the amount of 3,500,000 you want to find out what kind of phone to buy.In the cell to find enter the formula: = LOOKUP (E5, E5: E9, D5: D9) or you can enter the formula: = LOOKUP (3500000, E5: E9, D5: D9).

Note that in this case you should not use commas separated by numbers

How to use the LOOKUP function in excel? Picture 7

Press Enter -> With the amount of 3500000 you can choose the type of XIAOMI phone

How to use the LOOKUP function in excel? Picture 8

The amount of 6,500,000 is not in column E, but the function still takes the largest value in column E but is less than 6,500,000

In a cell to calculate, enter the formula: = LOOKUP (6500000, E5: E9, D5: D9)

How to use the LOOKUP function in excel? Picture 9

Press Enter -> for 6,500,000 you can buy an IPHONE phone

How to use the LOOKUP function in excel? Picture 10